AS
/******************************************************************************
NAME: cmg_order_releases_pck
PURPOSE: This package releases holds on the sales order lines depending
on certain dates. These holds are released to allow receivables
interface to pick the lines for invoicing.
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 5/23/2005 Jim Stuart Created this package.
1.1 6/3/05 J.Stuart Removed procedures release_return,release_adj,
release_allot. Added release_orderes.
1.2 6/29/05 J.Stuart Added parameters for from and to date.
1.3 09/24/05 Naveen Tati Added new Procedure to set the header attribs to null SCR 3673
2.0 10/25/2006 Naveen Tati Added additional check for Invoice Interface status code so that the transactions will not get interfaced into AR once again. SCR 5007
2.1 10/20/2010 Uday T SCR 6658
2.2 08/04/2014 Uday T Genera Foreign Project
******************************************************************************/
PROCEDURE release_orders (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_release_type IN fnd_flex_values.flex_value%TYPE,
p_cust_id IN oe_order_headers.sold_to_org_id%TYPE,
p_ord_id IN oe_order_headers.header_id%TYPE,
p_from_date IN oe_order_headers.ordered_date%TYPE,
p_to_date IN oe_order_headers.ordered_date%TYPE,
p_customer_type IN VARCHAR2
);
PROCEDURE wf_ord_release (
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout IN OUT VARCHAR2
);
PROCEDURE set_allotment_hdr_attribs_null;
END cmg_order_releases_pck;
/CREATE OR REPLACE PACKAGE APPS.cmg_order_releases_pck
AS
/******************************************************************************
NAME: cmg_order_releases_pck
PURPOSE: This package releases holds on the sales order lines depending
on certain dates. These holds are released to allow receivables
interface to pick the lines for invoicing.
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 5/23/2005 Jim Stuart Created this package.
1.1 6/3/05 J.Stuart Removed procedures release_return,release_adj,
release_allot. Added release_orderes.
1.2 6/29/05 J.Stuart Added parameters for from and to date.
1.3 09/24/05 Naveen Tati Added new Procedure to set the header attribs to null SCR 3673
2.0 10/25/2006 Naveen Tati Added additional check for Invoice Interface status code so that the transactions will not get interfaced into AR once again. SCR 5007
2.1 10/20/2010 Uday T SCR 6658
2.2 08/04/2014 Uday T Genera Foreign Project
******************************************************************************/
PROCEDURE release_orders (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_release_type IN fnd_flex_values.flex_value%TYPE,
p_cust_id IN oe_order_headers.sold_to_org_id%TYPE,
p_ord_id IN oe_order_headers.header_id%TYPE,
p_from_date IN oe_order_headers.ordered_date%TYPE,
p_to_date IN oe_order_headers.ordered_date%TYPE,
p_customer_type IN VARCHAR2
);
PROCEDURE wf_ord_release (
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout IN OUT VARCHAR2
);
PROCEDURE set_allotment_hdr_attribs_null;
END cmg_order_releases_pck;
/
CREATE OR REPLACE PACKAGE BODY APPS.cmg_order_releases_pck
AS
/******************************************************************************
NAME: cmg_order_releases_pck
PURPOSE: This package releases holds on the sales order lines depending
on certain dates. These holds are released to allow receivables
interface to pick the lines for invoicing.
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 5/23/2005 Jim Stuart Created this package.
1.1 6/3/05 J.Stuart Added procedure release_orders. It is the main driver for the release sales orders process.
1.2 6/17/05 J.Stuart Made the floowing changes per CR#20. Added validation procedures for customer, address, site, order type, item status and attributes, holds. Also added logic to support upgraded orders with new order cycle.
1.3 6/29/05 J.Stuart Added parameters for from and to date. Dates are only
used for allotment type orders.
1.4 7/5/05 J.Stuart Made changes to all three cursors for performance.
Added table wf_process_activities,wf_item_activity_statuses.
1.5 7/6/05 J.Stuart Fixed issue with return status for UPG_AN_1003. Made some
performance changes.
1.6 7/8/05 J.Stuart Added flag to run background process.
1.7 7/13/05 J.Stuart Fixed problem passing parameters in wrong order to procedure process_upgraded. Turned off some of the messages written to the report.
1.8 7/22/05 J.Stuart Changed validation to just send the lines that passed validation to receivables.
1.9 7/28/05 J.Stuart Added more detail to some of the error messages. Added check of customer currency code vs order.
2.0 10/25/2006 Naveen Tati Added additional check for Invoice Interface status code so that the transactions will not get interfaced into AR once again. SCR # 5007
2.1 10/20/2010 Uday T SCR 6658
2.2 1/26/2015 Vijay / Uday Genera Foreign Phase 2
******************************************************************************/
v_customer_type VARCHAR2 (1);
v_is_return VARCHAR2 (1) :='N'; --vijay 12/3/2014
-- cursor definitions
-- select orders of type return to be released to receivables.
CURSOR c_return_types (
p_ord_id IN oe_order_headers.header_id%TYPE,
p_cust_id IN oe_order_headers.sold_to_org_id%TYPE,
p_activity IN wf_item_activity_statuses_v.activity_name%TYPE
)
IS
SELECT ool.header_id, ool.line_id, ooh.order_number, ott.NAME,
ooh.sold_to_org_id customer_id,
ooh.invoice_to_org_id header_bill_to_id,
ooh.ship_to_org_id header_ship_to_id,
ooh.transactional_curr_code,
ool.invoice_to_org_id line_bill_to_id,
ool.ship_to_org_id line_ship_to_id, ool.inventory_item_id,
ooh.order_type_id, ool.ship_from_org_id, 'Y' send_to_ar,
ool.attribute4 bipad, ool.attribute1 issue, a.attribute3 customer_name, ool.unit_selling_price
FROM applsys.wf_item_activity_statuses@prodapps.world wias,
applsys.wf_process_activities@prodapps.world wpa,
apps.oe_order_lines@prodapps.world ool,
apps.oe_order_headers@prodapps.world ooh,
apps.oe_transaction_types@prodapps.world ott,
ar.hz_cust_accounts@prodapps.world a,
ar.hz_parties@prodapps.world p,
apps.FND_FLEX_VALUES_VL@prodapps.world v,
applsys.FND_FLEX_VALUE_SETS@prodapps.world s
-- gen_profitcenter_mapping_tbl gt --vijay 10/31/2014
WHERE wias.process_activity = wpa.instance_id
AND wpa.activity_item_type = wias.item_type
AND wias.activity_status = 'NOTIFIED'
AND wpa.activity_name = p_activity
AND wias.item_type = 'OEOL'
AND wias.item_key = TO_CHAR (ool.line_id)
AND NVL (ool.invoice_interface_status_code, 'N') = 'N'
AND ool.open_flag = 'Y'
AND ool.header_id = ooh.header_id
AND ooh.sold_to_org_id = NVL (p_cust_id, ooh.sold_to_org_id)
AND ooh.header_id = NVL (p_ord_id, ooh.header_id)
AND ooh.booked_flag = 'Y'
AND ooh.cancelled_flag = 'N'
AND ooh.open_flag = 'Y'
AND V.ENABLED_FLAG='Y'
AND v.flex_value_set_id=s.flex_value_set_id
and s.flex_value_set_name='GEN_RETURN_ORDERTYPES'
and ott.name=V.FLEX_VALUE
AND ooh.order_type_id = ott.transaction_type_id
AND ooh.sold_to_org_id = a.cust_account_id
AND a.party_id = p.party_id
--AND p.party_name LIKE 'A-%'
--AND p.party_name LIKE '''' || v_customer_type || '-%' || ''''
/* AND ott.NAME IN
('H-MONTHLY-US-R', 'H-MONTHLY-CN-R', 'H-MONTHLY-FN-R',
'C-MONTHLY-US-R', 'C-MONTHLY-CN-R', 'C-MONTHLY-FN-R',
'H-MONTHLY-US-RV', 'H-MONTHLY-CN-RV', 'H-MONTHLY-FN-RV',
'C-MONTHLY-US-RV', 'C-MONTHLY-CN-RV', 'C-MONTHLY-FN-RV',
'A-MONTHLY-US-R', 'A-MONTHLY-CN-R', 'A-MONTHLY-US-RV',
'A-MONTHLY-CN-RV','A-MONTHLY-FN-R','A-MONTHLY-FN-RV'
,'G-MONTHLY-FN-RV','G-MONTHLY-FN-R','1-MONTHLY-FN-RV','1-MONTHLY-FN-R') -- SCR 6658 Added the new order types */ -- removed hardcoding as part of SCR 8385
--AND p.party_name LIKE (v_customer_type || '-%');
AND SUBSTR (p.party_name, 1, 1) =
(NVL (v_customer_type, SUBSTR (p.party_name, 1, 1)))
-- vijay 10/31/2014
/* AND SUBSTR(gt.derived_ordertype,1,1) = decode(substr(ott.name,1,1),'H','C', substr(ott.name,1,1))
AND SUBSTR(gt.issue_profitcenter_id,1,4) = GET_ISSPC_FNC(ool.attribute4, ool.attribute1)
AND gt.wholesaler_profitcenter_id = (select profitcenter_id
from cmg_logo_mapping_vw
where customer_name = decode(substr(a.attribute3,1,1),'C','M', substr(a.attribute3,1,1)))
*/
;
-- select orders to release that are associated with adjustments
CURSOR c_adj_types (
p_ord_id IN oe_order_headers.header_id%TYPE,
p_cust_id IN oe_order_headers.sold_to_org_id%TYPE,
p_activity IN wf_item_activity_statuses_v.activity_name%TYPE
)
IS
SELECT ool.header_id, ool.line_id, ooh.order_number, ott.NAME,
ooh.sold_to_org_id customer_id,
ooh.invoice_to_org_id header_bill_to_id,
ooh.ship_to_org_id header_ship_to_id,
ooh.transactional_curr_code,
ool.invoice_to_org_id line_bill_to_id,
ool.ship_to_org_id line_ship_to_id, ool.inventory_item_id,
ooh.order_type_id, ool.ship_from_org_id, 'Y' send_to_ar,
ool.attribute4 bipad, ool.attribute1 issue, a.attribute3 customer_name, ool.unit_selling_price
FROM applsys.wf_item_activity_statuses@prodapps.world wias,
applsys.wf_process_activities@prodapps.world wpa,
apps.oe_order_lines@prodapps.world ool,
apps.oe_order_headers@prodapps.world ooh,
apps.oe_transaction_types@prodapps.world ott,
ar.hz_cust_accounts@prodapps.world a,
ar.hz_parties@prodapps.world p
-- gen_profitcenter_mapping_tbl gt --vijay 10/31/2014
WHERE wias.process_activity = wpa.instance_id
AND wpa.activity_item_type = wias.item_type
AND wias.activity_status = 'NOTIFIED'
AND wpa.activity_name = 'CMG_HOLD_SALES_ORDER_LINE'
AND wias.item_type = 'OEOL'
AND wias.item_key = TO_CHAR (ool.line_id)
AND NVL (ool.invoice_interface_status_code, 'N') = 'N'
AND ool.open_flag = 'Y'
AND ool.header_id = ooh.header_id
AND ott.NAME NOT LIKE '%-R'
AND ott.NAME NOT LIKE '%WEEKLY%'
AND ott.NAME NOT LIKE '%GALLEY'
AND ott.NAME NOT LIKE 'H-WK%'
AND ott.description LIKE 'ADJ%'
AND (TRUNC (TO_DATE (ool.attribute6,'DD/MM/YYYY')) <= TRUNC (SYSDATE) --vijay added format mask to 'TRUNC (TO_DATE (ool.attribute6))' 11/25/2014
OR ool.attribute6 IS NULL
)
AND ooh.sold_to_org_id = NVL ('', ooh.sold_to_org_id)
AND ooh.header_id = NVL ('', ooh.header_id)
AND ooh.booked_flag = 'Y'
AND ooh.cancelled_flag = 'N'
AND ooh.order_type_id = ott.transaction_type_id
AND ooh.open_flag = 'Y'
AND ooh.sold_to_org_id = a.cust_account_id
AND a.party_id = p.party_id
--AND p.party_name LIKE (v_customer_type || '-%');
AND SUBSTR (p.party_name, 1, 1) =
(NVL ('', SUBSTR (p.party_name, 1, 1))
)
AND ooh.ORDER_NUMBER in (12313824,
12341738,
12341739,
12341744,
12341748);
-- vijay 10/31/2014
/* AND SUBSTR(gt.derived_ordertype,1,1) = decode(substr(ott.name,1,1),'H','C', substr(ott.name,1,1))
AND SUBSTR(gt.issue_profitcenter_id,1,4) = GET_ISSPC_FNC(ool.attribute4, ool.attribute1)
AND gt.wholesaler_profitcenter_id = (select profitcenter_id
from cmg_logo_mapping_vw
where customer_name = decode(substr(a.attribute3,1,1),'C','M', substr(a.attribute3,1,1)))
*/
-- select the rest of the orders that need to be released
CURSOR c_allot_types (
p_ord_id IN oe_order_headers.header_id%TYPE,
p_cust_id IN oe_order_headers.sold_to_org_id%TYPE,
p_activity IN wf_item_activity_statuses_v.activity_name%TYPE,
p_from_date IN oe_order_headers.ordered_date%TYPE,
p_to_date IN oe_order_headers.ordered_date%TYPE
)
IS
SELECT ool.header_id, ool.line_id, ooh.order_number, ott.NAME,
ooh.sold_to_org_id customer_id,
ooh.invoice_to_org_id header_bill_to_id,
ooh.ship_to_org_id header_ship_to_id,
ooh.transactional_curr_code,
ool.invoice_to_org_id line_bill_to_id,
ool.ship_to_org_id line_ship_to_id, ool.inventory_item_id,
ooh.order_type_id, ool.ship_from_org_id, 'Y' send_to_ar,
ool.attribute4 bipad, ool.attribute1 issue, a.attribute3 customer_name, ool.unit_selling_price
FROM applsys.wf_item_activity_statuses@prodapps.world wias,
applsys.wf_process_activities@prodapps.world wpa,
apps.oe_order_lines@prodapps.world ool,
apps.oe_order_headers@prodapps.world ooh,
apps.oe_transaction_types@prodapps.world ott,
ar.hz_cust_accounts@prodapps.world a,
ar.hz_parties@prodapps.world p
-- gen_profitcenter_mapping_tbl gt --vijay 10/31/2014
WHERE wias.process_activity = wpa.instance_id
AND wpa.activity_item_type = wias.item_type
AND wias.activity_status = 'NOTIFIED'
AND wpa.activity_name = p_activity
AND wias.item_type = 'OEOL'
AND wias.item_key = TO_CHAR (ool.line_id)
AND ott.order_category_code = 'ORDER'
AND NVL (ool.invoice_interface_status_code, 'N') = 'N'
AND ool.open_flag = 'Y'
AND ool.header_id = ooh.header_id
AND ooh.booked_flag = 'Y'
AND ooh.cancelled_flag = 'N'
AND ott.description NOT LIKE 'ADJ%'
AND ooh.ordered_date BETWEEN NVL (p_from_date, ooh.ordered_date)
AND NVL (p_to_date, ooh.ordered_date)
AND ooh.sold_to_org_id = NVL (p_cust_id, ooh.sold_to_org_id)
AND ooh.header_id = NVL (p_ord_id, ooh.header_id)
AND ooh.order_type_id = ott.transaction_type_id
AND ooh.open_flag = 'Y'
AND ooh.sold_to_org_id = a.cust_account_id
AND a.party_id = p.party_id
--AND p.party_name LIKE (v_customer_type || '-%');
AND SUBSTR (p.party_name, 1, 1) =
(NVL (v_customer_type, SUBSTR (p.party_name, 1, 1))
)
-- vijay 10/31/2014
/* AND SUBSTR(gt.derived_ordertype,1,1) = decode(substr(ott.name,1,1),'H','C', substr(ott.name,1,1))
AND SUBSTR(gt.issue_profitcenter_id,1,4) = GET_ISSPC_FNC(ool.attribute4, ool.attribute1)
AND gt.wholesaler_profitcenter_id = (select profitcenter_id
from cmg_logo_mapping_vw
where customer_name = decode(substr(a.attribute3,1,1),'C','M', substr(a.attribute3,1,1)))
*/
;
CURSOR c_wf_lookup (p_lookup_type IN wf_lookups.lookup_type%TYPE)
IS
SELECT lookup_code
FROM apps.wf_lookups
WHERE lookup_type = p_lookup_type;
-- used to validate the site use ids on the order
CURSOR c_site (
p_site_id IN ra_site_uses.site_use_id%TYPE,
p_site_code IN ra_site_uses.site_use_code%TYPE
)
IS
SELECT rsu.status site_status, ra.status address_status
FROM apps.ra_addresses@prodapps.world ra, apps.ra_site_uses@prodapps.world rsu
WHERE ra.address_id = rsu.address_id
AND rsu.site_use_code = p_site_code
AND rsu.site_use_id = p_site_id;
-- used to validate the item setup
CURSOR c_item (p_item_id IN oe_order_lines.inventory_item_id%TYPE)
IS
SELECT mp.organization_code, msi.segment1,
msi.inventory_item_status_code, msi.invoiceable_item_flag,
msi.invoice_enabled_flag
FROM Apps.mtl_parameters@prodapps.world mp, APPS.mtl_system_items@prodapps.world msi
WHERE msi.organization_id = mp.organization_id
AND mp.organization_code IN ('HDG', 'MST')
AND msi.inventory_item_id = 67809
ORDER BY mp.organization_code;
-- used to check the item attribute setup in the ship from org.
CURSOR c_item_org (
p_item_id IN oe_order_lines.inventory_item_id%TYPE,
p_org_id IN oe_order_lines.ship_from_org_id%TYPE
)
IS
SELECT invoice_enabled_flag
FROM apps.mtl_system_items@prodapps.world
WHERE organization_id = p_org_id AND inventory_item_id = p_item_id;
-- used to check to see if there are any holds on the sales order
CURSOR c_holds (
p_header_id IN oe_order_headers.header_id%TYPE,
p_line_id IN oe_order_lines.line_id%TYPE
)
IS
SELECT line_id
FROM apps.oe_order_holds@prodapps.world
WHERE released_flag = 'N'
AND line_id = NVL (p_line_id, line_id)
AND header_id = p_header_id;
-- gets the customer currency code for comparison to the sales order
CURSOR c_cust_curr (
p_cust_id IN oe_order_headers.sold_to_org_id%TYPE,
p_site_id IN oe_order_headers.invoice_to_org_id%TYPE
)
IS
SELECT currency_code
FROM ar.hz_cust_profile_amts@prodapps.world
WHERE NVL (site_use_id, p_site_id) = p_site_id
AND cust_account_id = p_cust_id;
site_rec c_site%ROWTYPE;
item_org_rec c_item_org%ROWTYPE;
v_cust_status ra_customers.status%TYPE;
v_cust_name ra_customers.customer_name%TYPE;
v_cust_country ra_customers.attribute3%TYPE;
v_trans_country oe_transaction_types.NAME%TYPE;
v_trans_curr oe_transaction_types.currency_code%TYPE;
v_trans_cust_type_id oe_transaction_types.cust_trx_type_id%TYPE;
v_trans_inv_source_id oe_transaction_types.invoice_source_id%TYPE;
v_trans_name oe_transaction_types.NAME%TYPE;
v_ret_status VARCHAR2 (1) := fnd_api.g_miss_char;
v_msg_count NUMBER;
v_msg_data VARCHAR2 (240) := fnd_api.g_miss_char;
v_err_msg VARCHAR2 (240);
v_err_code NUMBER;
i INTEGER;
ddl_stmt VARCHAR2 (2000);
v_hold_header_id oe_order_headers.header_id%TYPE := NULL;
v_hold_activity wf_item_activity_statuses_v.activity_name%TYPE
:= 'CMG_HOLD_SALES_ORDER_LINE';
v_hold_release_code wf_lookups.lookup_type%TYPE := 'CMG_RELEASE_ORDER';
v_upg_release_code wf_lookups.lookup_type%TYPE := 'UPG_RT_1003';
v_upg_activity wf_item_activity_statuses_v.activity_name%TYPE
:= 'UPG_AN_1000';
passed_validation VARCHAR2 (1) := fnd_api.g_true;
invalid_rel_type EXCEPTION;
run_background VARCHAR2 (1) := fnd_api.g_false;
TYPE order_rec IS RECORD (
order_number oe_order_headers.order_number%TYPE,
type_name oe_transaction_types.NAME%TYPE
);
TYPE val_record IS RECORD (
header_id oe_order_headers.header_id%TYPE,
line_id oe_order_lines.line_id%TYPE,
order_number oe_order_headers.order_number%TYPE,
NAME oe_transaction_types.NAME%TYPE,
customer_id oe_order_headers.sold_to_org_id%TYPE,
header_bill_to_id oe_order_headers.invoice_to_org_id%TYPE,
header_ship_to_id oe_order_headers.ship_to_org_id%TYPE,
transactional_curr_code oe_order_headers.transactional_curr_code%TYPE,
line_bill_to_id oe_order_lines.invoice_to_org_id%TYPE,
line_ship_to_id oe_order_lines.ship_to_org_id%TYPE,
inventory_item_id oe_order_lines.inventory_item_id%TYPE,
order_type_id oe_order_headers.order_type_id%TYPE,
ship_from_org_id oe_order_lines.ship_from_org_id%TYPE,
send_to_ar VARCHAR2 (1),
-- added by vijay 12/1/2014 for profit center validation
bipad oe_order_lines.attribute4%TYPE,
issue oe_order_lines.attribute1%TYPE,
customer_name hz_cust_accounts.attribute3%TYPE,
unit_selling_price oe_order_lines.unit_selling_price%TYPE
);
TYPE order_tbl IS TABLE OF order_rec
INDEX BY BINARY_INTEGER;
TYPE val_ord_tbl IS TABLE OF val_record
INDEX BY BINARY_INTEGER;
TYPE item_tbl IS TABLE OF c_item%ROWTYPE
INDEX BY BINARY_INTEGER;
item_rec item_tbl;
ord_rec order_tbl;
val_rec val_ord_tbl;
FUNCTION get_completion_code (p_lookup_type IN wf_lookups.lookup_type%TYPE)
RETURN wf_lookups.lookup_code%TYPE
IS
v_hold wf_lookups.lookup_code%TYPE;
BEGIN
OPEN c_wf_lookup (p_lookup_type);
FETCH c_wf_lookup
INTO v_hold;
CLOSE c_wf_lookup;
RETURN v_hold;
END get_completion_code;
FUNCTION get_cust_curr (
p_cust_id IN oe_order_headers.sold_to_org_id%TYPE,
p_site_id IN oe_order_headers.invoice_to_org_id%TYPE
)
RETURN hz_cust_profile_amts.currency_code%TYPE
IS
v_hold hz_cust_profile_amts.currency_code%TYPE;
BEGIN
IF p_site_id IS NULL
THEN
OPEN c_cust_curr (p_cust_id, NULL);
ELSE
OPEN c_cust_curr (p_cust_id, p_site_id);
END IF;
FETCH c_cust_curr
INTO v_hold;
IF (c_cust_curr%NOTFOUND OR c_cust_curr%NOTFOUND IS NULL)
THEN
v_hold := NULL;
END IF;
CLOSE c_cust_curr;
RETURN NVL (v_hold, 'XXX');
EXCEPTION
WHEN OTHERS
THEN
IF c_cust_curr%ISOPEN
THEN
CLOSE c_cust_curr;
END IF;
RETURN 'XXX';
END get_cust_curr;
PROCEDURE check_customer (
p_cust_id IN ra_customers.customer_id%TYPE,
p_passed IN OUT VARCHAR2
)
IS
BEGIN
cmg_utility_pck.log_msg (' Inside check_customer', p_cust_id);
v_cust_status := NULL;
v_cust_country := NULL;
ddl_stmt := 'select status, customer_name, ';
ddl_stmt :=
ddl_stmt
|| '( case when substr(attribute3,instr(attribute3,''US''),2) = ''US'' then ''US'' ';
ddl_stmt :=
ddl_stmt
|| ' when substr(attribute3,instr(attribute3,''CN''),2) = ''CN'' then ''CN'' ';
ddl_stmt :=
ddl_stmt
|| ' when substr(attribute3,instr(attribute3,''FN''),2) = ''FN'' then ''FN'' ';
ddl_stmt := ddl_stmt || ' else ''XX'' ';
ddl_stmt := ddl_stmt || ' end ) country from ra_customers ';
ddl_stmt := ddl_stmt || 'where customer_id = :p_cust_id ';
EXECUTE IMMEDIATE ddl_stmt
INTO v_cust_status, v_cust_name, v_cust_country
USING IN p_cust_id;
IF v_cust_status <> 'A'
THEN
cmg_utility_pck.write_output ( ' Customer is inactive => '
|| v_cust_name
|| '- cust id => '
|| p_cust_id
);
p_passed := fnd_api.g_false;
END IF;
cmg_utility_pck.log_msg (' Leaving check_customer', p_passed);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
cmg_utility_pck.write_output (' No customer information found.');
p_passed := fnd_api.g_false;
WHEN OTHERS
THEN
cmg_utility_pck.write_output ( 'Check_customer err others => '
|| SQLERRM
);
p_passed := fnd_api.g_false;
END check_customer;
PROCEDURE check_trans_type (
p_trans_id IN oe_order_headers.order_type_id%TYPE,
p_cust_country IN ra_customers.attribute3%TYPE,
p_passed IN OUT VARCHAR2
)
IS
BEGIN
cmg_utility_pck.log_msg (' Inside check_trans_type', p_trans_id);
v_trans_curr := NULL;
v_trans_cust_type_id := NULL;
v_trans_inv_source_id := NULL;
v_trans_name := NULL;
v_trans_country := NULL;
ddl_stmt :=
'select currency_code,cust_trx_type_id,invoice_source_id,name, ';
ddl_stmt :=
ddl_stmt
|| '( case when substr(name,instr(name,''US''),2) = ''US'' then ''US'' ';
ddl_stmt :=
ddl_stmt
|| ' when substr(name,instr(name,''CN''),2) = ''CN'' then ''CN'' ';
ddl_stmt :=
ddl_stmt
|| ' when substr(name,instr(name,''FN''),2) = ''FN'' then ''FN'' ';
ddl_stmt := ddl_stmt || ' else ''YY'' ';
ddl_stmt := ddl_stmt || ' end ) country from oe_transaction_types ';
ddl_stmt := ddl_stmt || 'where transaction_type_id = :p_trans_id ';
EXECUTE IMMEDIATE ddl_stmt
INTO v_trans_curr, v_trans_cust_type_id,
v_trans_inv_source_id, v_trans_name, v_trans_country
USING IN p_trans_id;
/* if nvl(p_cust_country,'XX') <> nvl(v_trans_country,'ZZ') then
cmg_utility_pck.write_output(' Order type does not match customer order type country = > '||v_trans_name);
p_passed := FND_API.G_FALSE;
end if; */
IF NVL (v_trans_inv_source_id, -1) = -1
THEN
cmg_utility_pck.write_output ( ' Order type '
|| v_trans_name
|| ' invoice source is not assigned. '
);
p_passed := fnd_api.g_false;
END IF;
IF NVL (v_trans_cust_type_id, -1) = -1
THEN
cmg_utility_pck.write_output
( ' Order type '
|| v_trans_name
|| ' receivables transaction type not assigned. '
);
p_passed := fnd_api.g_false;
END IF;
cmg_utility_pck.log_msg (' Leaving check_trans_type', p_passed);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
cmg_utility_pck.write_output (' Order type not found.');
p_passed := fnd_api.g_false;
WHEN OTHERS
THEN
cmg_utility_pck.write_output ( ' Check_trans_type err others => '
|| SQLERRM
);
p_passed := fnd_api.g_false;
END check_trans_type;
PROCEDURE check_site (
p_site_id IN ra_site_uses.site_use_id%TYPE,
p_ord_num IN oe_order_headers.order_number%TYPE,
p_site_code IN ra_site_uses.site_use_code%TYPE,
p_passed IN OUT VARCHAR2
)
IS
BEGIN
cmg_utility_pck.log_msg (' Inside check_site', p_site_id);
OPEN c_site (p_site_id, p_site_code);
FETCH c_site
INTO site_rec;
IF (c_site%NOTFOUND OR c_site%NOTFOUND IS NULL)
THEN
RAISE NO_DATA_FOUND;
END IF;
CLOSE c_site;
IF site_rec.site_status <> 'A'
THEN
cmg_utility_pck.write_output
( ' '
|| p_site_code
|| ' Site address is not active for order => '
|| p_ord_num
);
p_passed := fnd_api.g_false;
END IF;
IF site_rec.address_status <> 'A'
THEN
cmg_utility_pck.write_output
( ' '
|| p_site_code
|| ' Address is not active for order => '
|| p_ord_num
);
p_passed := fnd_api.g_false;
END IF;
cmg_utility_pck.log_msg (' Leaving check_site', p_passed);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
IF c_site%ISOPEN
THEN
CLOSE c_site;
END IF;
cmg_utility_pck.write_output
( ' '
|| p_site_code
|| ' Site address not found for order => '
|| p_ord_num
);
p_passed := fnd_api.g_false;
WHEN OTHERS
THEN
IF c_site%ISOPEN
THEN
CLOSE c_site;
END IF;
cmg_utility_pck.write_output ( ' Check_site err others => '
|| p_site_code
|| ' - '
|| SQLERRM
);
p_passed := fnd_api.g_false;
END check_site;
PROCEDURE check_item (
p_item_id IN oe_order_lines.inventory_item_id%TYPE,
p_org_id IN oe_order_lines.ship_from_org_id%TYPE,
p_passed IN OUT VARCHAR2
)
IS
BEGIN
cmg_utility_pck.log_msg (' Inside check_item', p_item_id);
item_rec.DELETE;
FOR rec IN c_item (p_item_id)
LOOP
item_rec (item_rec.COUNT + 1).inventory_item_status_code :=
rec.inventory_item_status_code;
item_rec (item_rec.COUNT).invoiceable_item_flag :=
rec.invoiceable_item_flag;
item_rec (item_rec.COUNT).organization_code := rec.organization_code;
item_rec (item_rec.COUNT).segment1 := rec.segment1;
item_rec (item_rec.COUNT).invoice_enabled_flag :=
rec.invoice_enabled_flag;
END LOOP;
IF item_rec.COUNT > 1
THEN
IF item_rec (1).inventory_item_status_code <>
item_rec (2).inventory_item_status_code
THEN
cmg_utility_pck.write_output
( ' Item master and child item status do not match '
|| item_rec (1).segment1
|| ' - '
|| item_rec (2).inventory_item_status_code
|| ' - '
|| item_rec (1).inventory_item_status_code
);
p_passed := fnd_api.g_false;
END IF;
IF item_rec (1).invoice_enabled_flag = 'N'
THEN
cmg_utility_pck.write_output ( ' Item '
|| item_rec (1).segment1
|| ' is not invoice enabled in '
|| item_rec (1).organization_code
|| ' organization.'
);
p_passed := fnd_api.g_false;
END IF;
ELSE
IF item_rec (1).organization_code = 'MST'
THEN
cmg_utility_pck.write_output ( ' Item '
|| item_rec (1).segment1
|| ' is not assigned to '
|| item_rec (1).organization_code
);
p_passed := fnd_api.g_false;
END IF;
END IF;
cmg_utility_pck.log_msg (' Leaving check_item', p_passed);
END check_item;
PROCEDURE check_holds (
p_header_id IN oe_order_headers.header_id%TYPE,
p_line_id IN oe_order_lines.line_id%TYPE,
p_ord_num IN oe_order_headers.order_number%TYPE,
p_passed IN OUT VARCHAR2
)
IS
v_hold oe_order_lines.line_id%TYPE;
BEGIN
cmg_utility_pck.log_msg (' Inside check_holds',
p_header_id || ' - ' || p_line_id
);
OPEN c_holds (p_header_id, p_line_id);
FETCH c_holds
INTO v_hold;
IF c_holds%FOUND
THEN
IF (p_line_id IS NULL AND v_hold IS NULL)
THEN
cmg_utility_pck.write_output ( ' Order is on hold for => '
|| p_ord_num
);
p_passed := fnd_api.g_false;
END IF;
IF (p_line_id IS NOT NULL AND v_hold IS NOT NULL)
THEN
cmg_utility_pck.write_output ( ' Line is on hold for order => '
|| p_ord_num
|| ' Line id => '
|| p_line_id
);
p_passed := fnd_api.g_false;
END IF;
END IF;
cmg_utility_pck.log_msg (' Leaving check_holds', p_passed);
CLOSE c_holds;
EXCEPTION
WHEN OTHERS
THEN
IF c_holds%ISOPEN
THEN
CLOSE c_holds;
END IF;
cmg_utility_pck.write_output ( ' Error check_holds other => '
|| SQLERRM
);
p_passed := fnd_api.g_false;
END check_holds;
PROCEDURE validate_order (p_rec IN OUT val_ord_tbl)
IS
-- currency code validation for transaction type & customer currency vijay 11/19/2014
v_curr_code oe_transaction_types.currency_code%TYPE;
v_trans_type oe_transaction_types.name%TYPE;
CURSOR curr (p_order_type_id in oe_order_headers.order_type_id%TYPE) IS
SELECT currency_code, name
FROM apps.oe_transaction_types@prodapps.world
WHERE transaction_type_id = p_order_type_id;
-- profitcenter validation for derived order type, issue pc, wholesaler pc vijay 12/1/2014
v_iss_pc hdg.gen_profitcenter_mapping_tbl.issue_profitcenter_id%TYPE;
v_wh_pc hdg.gen_profitcenter_mapping_tbl.wholesaler_profitcenter_id%TYPE;
v_pc_id cmg_logo_mapping_vw.profitcenter_id%TYPE;
CURSOR profit_cur (p_name IN oe_transaction_types.NAME%TYPE) IS
SELECT issue_profitcenter_id, wholesaler_profitcenter_id
FROM hdg.gen_profitcenter_mapping_tbl@prodapps.world
WHERE SUBSTR(derived_ordertype,1,1) = decode(substr(p_name,1,1),'H','C', substr(p_name,1,1));
--
v_passed VARCHAR2 (1) := 'N';
BEGIN
cmg_utility_pck.log_msg (' Inside validate_order', NULL);
FOR i IN 1 .. p_rec.COUNT
LOOP
IF i = 1
THEN
-- cmg_utility_pck.write_output(' ');
-- cmg_utility_pck.write_output('Validating order number => '||p_rec(i).order_number||' - '||
-- p_rec(i).name);
check_customer (p_rec (i).customer_id, passed_validation);
check_trans_type (p_rec (i).order_type_id,
v_cust_country,
passed_validation
);
check_site (p_rec (i).header_bill_to_id,
p_rec (i).order_number,
'BILL_TO',
passed_validation
);
check_site (p_rec (i).header_ship_to_id,
p_rec (i).order_number,
'SHIP_TO',
passed_validation
);
check_holds (p_rec (i).header_id,
NULL,
p_rec (i).order_number,
passed_validation
);
IF p_rec (i).transactional_curr_code <>
get_cust_curr (p_rec (i).customer_id,
p_rec (i).header_bill_to_id
)
THEN
cmg_utility_pck.write_output
( ' Order '
|| p_rec (i).order_number
|| ' currency code '
|| p_rec (i).transactional_curr_code
|| ' does not match customer currency '
|| get_cust_curr
(p_rec (i).customer_id,
p_rec (i).header_bill_to_id
)
);
passed_validation := fnd_api.g_false;
END IF;
-- currency code validation for transaction type & customer currency at order level vijay 11/19/2014
OPEN curr(p_rec(i).order_type_id);
FETCH curr INTO v_curr_code, v_trans_type;
IF v_curr_code <>
get_cust_curr (p_rec (i).customer_id,
p_rec (i).header_bill_to_id
)
THEN
cmg_utility_pck.write_output
( ' Order '
|| p_rec (i).order_number
|| ' Transaction Type'
|| v_trans_type
|| ' currency code '
|| v_curr_code
|| ' does not match customer currency '
|| get_cust_curr
(p_rec (i).customer_id,
p_rec (i).header_bill_to_id
)
);
passed_validation := fnd_api.g_false;
END IF;
CLOSE curr;
END IF;
-- for returns, if the unit selling price is zero skip profit center validation-- vijay 12/3/2014
IF v_is_return = 'Y'
THEN
IF p_rec(i).unit_selling_price = 0
THEN
goto skippc;
END IF;
END IF;
-- profit center validation for orders vijay 12/1/2014
OPEN profit_cur(p_rec(i).NAME);
FETCH profit_cur INTO v_iss_pc, v_wh_pc;
IF SUBSTR(v_iss_pc,1,4) <> GET_ISSPC_FNC(p_rec(i).bipad,p_rec(i).issue)
THEN
/* cmg_utility_pck.write_output
( ' Order '
|| p_rec (i).order_number
|| ' Issue Profit Center ID '
|| GET_ISSPC_FNC(p_rec(i).bipad,p_rec(i).issue)
|| ' does not match '
|| v_iss_pc
|| ' for order type '
|| p_rec(i).NAME
); */
if v_is_return = 'Y' then
cmg_utility_pck.write_output
( ' Order '
|| p_rec (i).order_number
||' >> Mismatch in Profit Center ID for order type '
|| p_rec(i).NAME
||'>> Reverse the tally and cancel the sales order,once done reprocess the return(ppsb/manual) with correct combination'
);
-- Reversse the tally and cancel the order and reporcess as a new return(ppsb)
else
cmg_utility_pck.write_output
( ' Order '
|| p_rec (i).order_number
||' >> Mismatch in Profit Center ID for order type '
|| p_rec(i).NAME
||'>> Cancel the sales order and reprocess the order with correct combination'
);
end if;
passed_validation := fnd_api.g_false;
END IF;
select profitcenter_id into v_pc_id
from cmg_logo_mapping_vw
where customer_name = decode(substr(p_rec(i).customer_name,1,1),'C','M', substr(p_rec(i).customer_name,1,1));
IF v_wh_pc <> v_pc_id
THEN
/* cmg_utility_pck.write_output
( ' Order '
|| p_rec (i).order_number
|| ' Wholesaler Profit Center ID '
|| v_pc_id
|| ' does not match '
|| v_wh_pc
|| ' for order type '
|| p_rec(i).NAME
); */
if v_is_return ='Y' then
cmg_utility_pck.write_output
( ' Order '
|| p_rec (i).order_number
||' >> Wrong Order Type '
|| p_rec(i).NAME
||' for this Customer '
-- ||'>> Cancel the sales order and reprocess the order with correct combination'
||'>> Reverse the tally and cancel the sales order,once done reprocess the return(ppsb/manual) with correct combination'
);
else
cmg_utility_pck.write_output
( ' Order '
|| p_rec (i).order_number
||' >> Wrong Order Type '
|| p_rec(i).NAME
||' for this Customer '
||'>> Cancel the sales order and reprocess the order with correct combination'
);
end if;
passed_validation := fnd_api.g_false;
END IF;
CLOSE profit_cur;
<<skippc>>
v_passed := 'Y';
-- cmg_utility_pck.write_output(' ');
-- cmg_utility_pck.write_output(' Validating line information => '||p_rec(i).line_id);
check_item (p_rec (i).inventory_item_id,
p_rec (i).ship_from_org_id,
v_passed
);
check_site (p_rec (i).line_bill_to_id,
p_rec (i).order_number,
'BILL_TO',
v_passed
);
check_site (p_rec (i).line_ship_to_id,
p_rec (i).order_number,
'SHIP_TO',
v_passed
);
check_holds (p_rec (i).header_id,
p_rec (i).line_id,
p_rec (i).order_number,
v_passed
);
p_rec (i).send_to_ar := v_passed;
END LOOP;
cmg_utility_pck.log_msg (' Leaving validate_order', passed_validation);
END validate_order;
PROCEDURE load_ord (
p_ord_num IN oe_order_headers.order_number%TYPE,
p_type_name IN oe_transaction_types.NAME%TYPE
)
IS
v_exists VARCHAR2 (1);
BEGIN
v_exists := 'N';
IF ord_rec.COUNT > 0
THEN
FOR x IN ord_rec.FIRST .. ord_rec.LAST
LOOP
IF ord_rec (x).order_number = p_ord_num
THEN
v_exists := 'Y';
END IF;
END LOOP;
END IF;
IF v_exists = 'N'
THEN
ord_rec (ord_rec.COUNT + 1).order_number := p_ord_num;
ord_rec (ord_rec.COUNT).type_name := p_type_name;
END IF;
END load_ord;
PROCEDURE val_table (p_rec IN c_return_types%ROWTYPE)
IS
BEGIN
cmg_utility_pck.log_msg (' Inside val_table', p_rec.header_id);
val_rec (val_rec.COUNT + 1).header_id := p_rec.header_id;
val_rec (val_rec.COUNT).line_id := p_rec.line_id;
val_rec (val_rec.COUNT).order_number := p_rec.order_number;
val_rec (val_rec.COUNT).NAME := p_rec.NAME;
val_rec (val_rec.COUNT).customer_id := p_rec.customer_id;
val_rec (val_rec.COUNT).header_bill_to_id := p_rec.header_bill_to_id;
val_rec (val_rec.COUNT).header_ship_to_id := p_rec.header_ship_to_id;
val_rec (val_rec.COUNT).transactional_curr_code :=
p_rec.transactional_curr_code;
val_rec (val_rec.COUNT).line_bill_to_id := p_rec.line_bill_to_id;
val_rec (val_rec.COUNT).line_ship_to_id := p_rec.line_ship_to_id;
val_rec (val_rec.COUNT).inventory_item_id := p_rec.inventory_item_id;
val_rec (val_rec.COUNT).order_type_id := p_rec.order_type_id;
val_rec (val_rec.COUNT).ship_from_org_id := p_rec.ship_from_org_id;
val_rec (val_rec.COUNT).send_to_ar := p_rec.send_to_ar;
-- added by vijay on 12/1/2014 for profit center validation
val_rec (val_rec.COUNT).bipad := p_rec.bipad;
val_rec (val_rec.COUNT).issue := p_rec.issue;
val_rec (val_rec.COUNT).customer_name := p_rec.customer_name;
val_rec (val_rec.COUNT).unit_selling_price := p_rec.unit_selling_price;
cmg_utility_pck.log_msg (' Leaving val_table', p_rec.header_id);
EXCEPTION
WHEN OTHERS
THEN
cmg_utility_pck.log_msg (' Err others', SQLERRM);
END val_table;
PROCEDURE send_to_receivables (
p_rec IN val_ord_tbl,
p_activity IN VARCHAR2,
p_compl_code IN VARCHAR2,
p_ret_status OUT VARCHAR2
)
IS
BEGIN
cmg_utility_pck.log_msg (' Inside send_to_receivables',
p_activity || ' - ' || p_compl_code
);
FOR i IN 1 .. p_rec.COUNT
LOOP
cmg_utility_pck.log_msg (' Releasing line ', p_rec (i).line_id);
IF p_rec (i).send_to_ar = 'Y'
THEN
wf_engine.completeactivityinternalname
(itemtype => 'OEOL',
itemkey => TO_CHAR (p_rec (i).line_id),
activity => p_activity,
RESULT => get_completion_code
(p_compl_code)
);
load_ord (p_rec (i).order_number, p_rec (i).NAME);
END IF;
END LOOP;
p_ret_status := fnd_api.g_ret_sts_success; -- return 'S'
EXCEPTION
WHEN OTHERS
THEN
p_ret_status := fnd_api.g_ret_sts_error; -- return 'E'
END send_to_receivables;
PROCEDURE workflow_background
IS
v_errbuf_out VARCHAR2 (250);
v_retcode_out VARCHAR2 (1);
BEGIN
wf_engine.backgroundconcurrent (errbuf => v_errbuf_out,
retcode => v_retcode_out,
itemtype => 'OEOL',
-- minthreshold in varchar2,
-- maxthreshold in varchar2,
process_deferred => 'Y',
process_timeout => 'Y',
process_stuck => 'Y'
);
IF v_retcode_out = '2'
THEN
cmg_utility_pck.write_output
( ' Error in workflow background process => '
|| v_errbuf_out
);
END IF;
END workflow_background;
PROCEDURE process_upgraded (
p_release_type IN VARCHAR2,
p_ord_id IN oe_order_headers.sold_to_org_id%TYPE,
p_cust_id IN oe_order_headers.header_id%TYPE,
p_from_date IN oe_order_headers.ordered_date%TYPE,
p_to_date IN oe_order_headers.ordered_date%TYPE
)
IS
BEGIN
cmg_utility_pck.log_msg (' Inside process_upgraded', p_release_type);
run_background := fnd_api.g_false;
passed_validation := fnd_api.g_true;
IF p_release_type = 'RETURNS'
THEN
FOR rec IN c_return_types (p_ord_id, p_cust_id, v_upg_activity)
LOOP
IF (v_hold_header_id IS NULL OR v_hold_header_id != rec.header_id
)
THEN
IF val_rec.COUNT > 0
THEN
validate_order (val_rec);
IF passed_validation = fnd_api.g_true
THEN
send_to_receivables (val_rec,
'UPG_AN_1003',
v_upg_release_code,
v_ret_status
);
run_background := fnd_api.g_true;
END IF;
passed_validation := fnd_api.g_true;
val_rec.DELETE;
END IF;
v_hold_header_id := rec.header_id;
END IF;
val_table (rec);
END LOOP;
IF val_rec.COUNT > 0
THEN
validate_order (val_rec);
IF passed_validation = fnd_api.g_true
THEN
send_to_receivables (val_rec,
'UPG_AN_1003',
v_upg_release_code,
v_ret_status
);
run_background := fnd_api.g_true;
END IF;
END IF;
ELSIF p_release_type = 'ALLOTMENTS'
THEN
cmg_utility_pck.log_msg (' Processing allotment',
p_ord_id
|| ' - '
|| v_upg_activity
|| ' - '
|| p_from_date
|| ' - '
|| p_to_date
);
FOR rec IN c_allot_types (p_ord_id,
p_cust_id,
v_upg_activity,
p_from_date,
p_to_date
)
LOOP
cmg_utility_pck.log_msg (' Rec header id', rec.header_id);
IF (v_hold_header_id IS NULL OR v_hold_header_id != rec.header_id
)
THEN
IF val_rec.COUNT > 0
THEN
validate_order (val_rec);
IF passed_validation = fnd_api.g_true
THEN
send_to_receivables (val_rec,
'UPG_AN_1003',
v_upg_release_code,
v_ret_status
);
run_background := fnd_api.g_true;
END IF;
passed_validation := fnd_api.g_true;
val_rec.DELETE;
END IF;
v_hold_header_id := rec.header_id;
END IF;
val_table (rec);
END LOOP;
cmg_utility_pck.log_msg (' Allot count', val_rec.COUNT);
IF val_rec.COUNT > 0
THEN
validate_order (val_rec);
IF passed_validation = fnd_api.g_true
THEN
send_to_receivables (val_rec,
'UPG_AN_1003',
v_upg_release_code,
v_ret_status
);
run_background := fnd_api.g_true;
END IF;
END IF;
ELSIF p_release_type = 'ADJUSTMENTS'
THEN
FOR rec IN c_adj_types (p_ord_id, p_cust_id, v_upg_activity)
LOOP
IF (v_hold_header_id IS NULL OR v_hold_header_id != rec.header_id
)
THEN
IF val_rec.COUNT > 0
THEN
validate_order (val_rec);
IF passed_validation = fnd_api.g_true
THEN
send_to_receivables (val_rec,
'UPG_AN_1003',
v_upg_release_code,
v_ret_status
);
run_background := fnd_api.g_true;
END IF;
passed_validation := fnd_api.g_true;
val_rec.DELETE;
END IF;
v_hold_header_id := rec.header_id;
END IF;
val_table (rec);
END LOOP;
IF val_rec.COUNT > 0
THEN
validate_order (val_rec);
IF passed_validation = fnd_api.g_true
THEN
send_to_receivables (val_rec,
'UPG_AN_1003',
v_upg_release_code,
v_ret_status
);
run_background := fnd_api.g_true;
END IF;
END IF;
END IF;
IF run_background = fnd_api.g_true
THEN
workflow_background;
END IF;
cmg_utility_pck.log_msg (' Leaving process_upgraded', passed_validation);
END process_upgraded;
-- start this puppy
PROCEDURE release_return (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_ord_id IN oe_order_headers.header_id%TYPE,
p_cust_id IN oe_order_headers.sold_to_org_id%TYPE
)
IS
BEGIN
cmg_utility_pck.log_msg
('CMG_ORDER_RELEASES_PCK.RELEASE_RETURN: Started',
TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
);
cmg_utility_pck.write_output
(' Report for Returns Receivables Interfaced ');
cmg_utility_pck.write_output
('----------------------------------------------------');
cmg_utility_pck.write_output ('');
passed_validation := fnd_api.g_true;
FOR rec IN c_return_types (p_ord_id, p_cust_id, v_hold_activity)
LOOP
cmg_utility_pck.log_msg (' Header id ', rec.header_id);
cmg_utility_pck.log_msg (' Hold header id', v_hold_header_id);
IF (v_hold_header_id IS NULL OR v_hold_header_id != rec.header_id)
THEN
IF val_rec.COUNT > 0
THEN
v_is_return := 'Y'; -- set flag
validate_order (val_rec);
v_is_return := 'N'; -- reset flag
IF passed_validation = fnd_api.g_true
THEN
send_to_receivables (val_rec,
'CMG_HOLD_SALES_ORDER_LINE',
v_hold_release_code,
v_ret_status
);
END IF;
passed_validation := fnd_api.g_true;
val_rec.DELETE;
END IF;
v_hold_header_id := rec.header_id;
END IF;
val_table (rec);
END LOOP;
IF val_rec.COUNT > 0
THEN
v_is_return := 'Y'; -- set flag
validate_order (val_rec);
v_is_return := 'N'; -- reset flag
IF passed_validation = fnd_api.g_true
THEN
send_to_receivables (val_rec,
'CMG_HOLD_SALES_ORDER_LINE',
v_hold_release_code,
v_ret_status
);
END IF;
END IF;
val_rec.DELETE;
-- process orders that are in the upgraded workflow
-- these records will slowly go any
process_upgraded ('RETURNS', p_ord_id, p_cust_id, NULL, NULL);
cmg_utility_pck.write_output (' ');
IF ord_rec.COUNT > 0
THEN
FOR i IN ord_rec.FIRST .. ord_rec.LAST
LOOP
cmg_utility_pck.write_output ( 'Order released => '
|| ord_rec (i).order_number
|| ' - '
|| ord_rec (i).type_name
);
END LOOP;
ELSE
cmg_utility_pck.write_output ('No Orders Released to Receivables');
END IF;
COMMIT;
cmg_utility_pck.write_output ('');
cmg_utility_pck.write_output
('----------------------------------------------------');
cmg_utility_pck.write_output (' End of Report ');
errbuf := 'CMG_ORDER_RELEASES_PCK.RELEASE_RETURN: Completed';
retcode := SQLCODE;
EXCEPTION
WHEN OTHERS
THEN
errbuf :=
SUBSTR
( 'CMG_ORDER_RELEASES_PCK.RELEASE_RETURN: Err others => '
|| SQLERRM,
1,
150
);
retcode := SQLCODE;
ROLLBACK;
END release_return;
PROCEDURE release_adj (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_ord_id IN oe_order_headers.header_id%TYPE,
p_cust_id IN oe_order_headers.sold_to_org_id%TYPE
)
IS
BEGIN
cmg_utility_pck.log_msg ('CMG_ORDER_RELEASES_PCK.RELEASE_ADJ: Started',
TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
);
cmg_utility_pck.write_output
(' Report for Adjustments Receivables Interfaced ');
cmg_utility_pck.write_output
('----------------------------------------------------');
cmg_utility_pck.write_output ('');
passed_validation := fnd_api.g_true;
FOR rec IN c_adj_types (p_ord_id, p_cust_id, v_hold_activity)
LOOP
cmg_utility_pck.log_msg (' Header id ', rec.header_id);
cmg_utility_pck.log_msg (' Hold header id', v_hold_header_id);
IF (v_hold_header_id IS NULL OR v_hold_header_id != rec.header_id)
THEN
IF val_rec.COUNT > 0
THEN
validate_order (val_rec);
IF passed_validation = fnd_api.g_true
THEN
send_to_receivables (val_rec,
'CMG_HOLD_SALES_ORDER_LINE',
v_hold_release_code,
v_ret_status
);
END IF;
passed_validation := fnd_api.g_true;
val_rec.DELETE;
END IF;
v_hold_header_id := rec.header_id;
END IF;
val_table (rec);
END LOOP;
IF val_rec.COUNT > 0
THEN
validate_order (val_rec);
IF passed_validation = fnd_api.g_true
THEN
send_to_receivables (val_rec,
'CMG_HOLD_SALES_ORDER_LINE',
v_hold_release_code,
v_ret_status
);
END IF;
END IF;
val_rec.DELETE;
-- process orders that are in the upgraded workflow
-- these records will slowly go any
process_upgraded ('ADJUSTMENTS', p_ord_id, p_cust_id, NULL, NULL);
cmg_utility_pck.write_output (' ');
IF ord_rec.COUNT > 0
THEN
FOR i IN ord_rec.FIRST .. ord_rec.LAST
LOOP
cmg_utility_pck.write_output ( 'Order released => '
|| ord_rec (i).order_number
|| ' - '
|| ord_rec (i).type_name
);
END LOOP;
ELSE
cmg_utility_pck.write_output ('No Orders Released to Receivables');
END IF;
COMMIT;
cmg_utility_pck.write_output ('');
cmg_utility_pck.write_output
('----------------------------------------------------');
cmg_utility_pck.write_output (' End of Report ');
errbuf := 'CMG_ORDER_RELEASES_PCK.RELEASE_ADJ: Completed';
retcode := SQLCODE;
EXCEPTION
WHEN OTHERS
THEN
errbuf :=
SUBSTR ( 'CMG_ORDER_RELEASES_PCK.RELEASE_ADJ: Err others => '
|| SQLERRM,
1,
150
);
retcode := SQLCODE;
ROLLBACK;
END release_adj;
PROCEDURE release_allot (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_ord_id IN oe_order_headers.header_id%TYPE,
p_cust_id IN oe_order_headers.sold_to_org_id%TYPE,
p_from_date IN oe_order_headers.ordered_date%TYPE,
p_to_date IN oe_order_headers.ordered_date%TYPE
)
IS
BEGIN
cmg_utility_pck.log_msg
('CMG_ORDER_RELEASES_PCK.RELEASE_ALLOT: Started',
TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
);
cmg_utility_pck.log_msg ('Parameters passed:', NULL);
cmg_utility_pck.log_msg (' p_ord_id ', p_ord_id);
cmg_utility_pck.log_msg (' p_cust_id ', p_cust_id);
cmg_utility_pck.log_msg (' p_from_date', p_from_date);
cmg_utility_pck.log_msg (' p_to_date ', p_to_date);
cmg_utility_pck.write_output
(' Report for Allotments Receivables Interfaced ');
cmg_utility_pck.write_output
('----------------------------------------------------');
cmg_utility_pck.write_output ('');
passed_validation := fnd_api.g_true;
FOR rec IN c_allot_types (p_ord_id,
p_cust_id,
v_hold_activity,
p_from_date,
p_to_date
)
LOOP
cmg_utility_pck.log_msg (' Header id ', rec.header_id);
cmg_utility_pck.log_msg (' Hold header id', v_hold_header_id);
IF (v_hold_header_id IS NULL OR v_hold_header_id != rec.header_id)
THEN
IF val_rec.COUNT > 0
THEN
validate_order (val_rec);
IF passed_validation = fnd_api.g_true
THEN
send_to_receivables (val_rec,
'CMG_HOLD_SALES_ORDER_LINE',
v_hold_release_code,
v_ret_status
);
END IF;
passed_validation := fnd_api.g_true;
val_rec.DELETE;
END IF;
v_hold_header_id := rec.header_id;
END IF;
val_table (rec);
END LOOP;
IF val_rec.COUNT > 0
THEN
validate_order (val_rec);
IF passed_validation = fnd_api.g_true
THEN
send_to_receivables (val_rec,
'CMG_HOLD_SALES_ORDER_LINE',
v_hold_release_code,
v_ret_status
);
END IF;
END IF;
val_rec.DELETE;
-- process orders that are in the upgraded workflow
-- these records will slowly go any
cmg_utility_pck.log_msg (' Before call to Process_upgraded', p_ord_id);
process_upgraded ('ALLOTMENTS',
p_ord_id,
p_cust_id,
p_from_date,
p_to_date
);
cmg_utility_pck.write_output (' ');
IF ord_rec.COUNT > 0
THEN
FOR i IN ord_rec.FIRST .. ord_rec.LAST
LOOP
cmg_utility_pck.write_output ( 'Order released => '
|| ord_rec (i).order_number
|| ' - '
|| ord_rec (i).type_name
);
END LOOP;
ELSE
cmg_utility_pck.write_output ('No Orders Released to Receivables');
END IF;
COMMIT;
cmg_utility_pck.write_output ('');
cmg_utility_pck.write_output
('----------------------------------------------------');
cmg_utility_pck.write_output (' End of Report ');
errbuf := 'CMG_ORDER_RELEASES_PCK.RELEASE_ALLOT: Completed';
retcode := SQLCODE;
EXCEPTION
WHEN OTHERS
THEN
errbuf :=
SUBSTR
( 'CMG_ORDER_RELEASES_PCK.RELEASE_ALLOT: Err others => '
|| SQLERRM,
1,
150
);
retcode := SQLCODE;
ROLLBACK;
END release_allot;
PROCEDURE wf_ord_release (
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout IN OUT VARCHAR2
)
IS
BEGIN
--
-- RUN mode - normal process execution
--
IF (funcmode = 'RUN')
THEN
-- Return Notified
resultout :=
wf_engine.eng_notified
|| ':'
|| wf_engine.eng_null
|| ':'
|| wf_engine.eng_null;
RETURN; -- Order should go thru normal process flow.
END IF;
IF (funcmode = 'CANCEL')
THEN
NULL;
-- no result needed
resultout := 'COMPLETE';
RETURN;
END IF;
EXCEPTION
WHEN OTHERS
THEN
-- The line below records this function call in the error system
-- in the case of an exception.
wf_core.CONTEXT ('CMG_ORDER_RELEASES_PCK',
'WF_ORD_RELEASE',
itemtype,
itemkey,
TO_CHAR (actid),
funcmode
);
RAISE;
END wf_ord_release;
PROCEDURE release_orders (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_release_type IN fnd_flex_values.flex_value%TYPE,
p_cust_id IN oe_order_headers.sold_to_org_id%TYPE,
p_ord_id IN oe_order_headers.header_id%TYPE,
p_from_date IN oe_order_headers.ordered_date%TYPE,
p_to_date IN oe_order_headers.ordered_date%TYPE,
p_customer_type IN VARCHAR2
)
IS
BEGIN
v_customer_type := p_customer_type;
cmg_utility_pck.write_output (' customer : ' || v_customer_type);
IF p_release_type = 'ALLOTMENTS'
THEN
release_allot (errbuf => v_err_msg,
retcode => v_err_code,
p_ord_id => p_ord_id,
p_cust_id => p_cust_id,
p_from_date => p_from_date,
p_to_date => p_to_date
);
set_allotment_hdr_attribs_null;
ELSIF p_release_type = 'RETURNS'
THEN
release_return (errbuf => v_err_msg,
retcode => v_err_code,
p_ord_id => p_ord_id,
p_cust_id => p_cust_id
);
ELSIF p_release_type = 'ADJUSTMENTS'
THEN
release_adj (errbuf => v_err_msg,
retcode => v_err_code,
p_ord_id => p_ord_id,
p_cust_id => p_cust_id
);
ELSE
RAISE invalid_rel_type;
END IF;
errbuf :=
'CMG_ORDER_RELEASES_PCK.RELEASE_ORDERS: Completed ' || v_err_msg;
retcode := NVL (v_err_code, SQLCODE);
EXCEPTION
WHEN invalid_rel_type
THEN
errbuf :=
'CMG_ORDER_RELEASES_PCK.RELEASE_ORDERS: Invalid sales release type => '
|| p_release_type;
retcode := 2;
WHEN OTHERS
THEN
errbuf :=
SUBSTR
( 'CMG_ORDER_RELEASES_PCK.RELEASE_ORDERS: Err others => '
|| SQLERRM,
1,
150
);
retcode := SQLCODE;
ROLLBACK;
END release_orders;
PROCEDURE set_allotment_hdr_attribs_null
IS
BEGIN
UPDATE ra_interface_lines
SET header_attribute1 = NULL,
header_attribute2 = NULL,
header_attribute3 = NULL,
header_attribute4 = NULL,
header_attribute5 = NULL,
header_attribute6 = NULL,
header_attribute7 = NULL,
header_attribute8 = NULL,
header_attribute9 = NULL,
header_attribute10 = NULL,
header_attribute11 = NULL,
header_attribute12 = NULL,
header_attribute13 = NULL,
header_attribute14 = NULL,
header_attribute15 = NULL
WHERE batch_source_name = 'HDG-MO-ORDERS';
END set_allotment_hdr_attribs_null;
END cmg_order_releases_pck;
/
No comments:
Post a Comment