[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT hca.cust_account_id cust_account_id
INTO l_cust_acct_id
FROM hz_cust_accounts hca
WHERE hca.party_id = CLN_SYNCITEM_PKG.GET_PARTY_ID();
SELECT concatenated_segments
FROM CLN_ITEMMST_ITEMHEADER_V
WHERE ORGANIZATION_ID= p_inventory_org_id
AND ( p_category_set_id IS NULL OR
p_category_set_id IN
( SELECT mcsvc.category_set_id
FROM mtl_item_categories mic, mtl_category_set_valid_cats mcsvc
WHERE mcsvc.category_set_id = mic.category_set_id AND
mic.inventory_item_id = CLN_ITEMMST_ITEMHEADER_V.INVENTORY_ITEM_ID AND
mic.organization_id = p_inventory_org_id
)
)
AND ( p_category_id IS NULL OR
p_category_id IN
( SELECT mcsvc.category_id
FROM mtl_item_categories mic, mtl_category_set_valid_cats mcsvc
WHERE mcsvc.category_id = mic.category_id AND
mic.inventory_item_id = CLN_ITEMMST_ITEMHEADER_V.INVENTORY_ITEM_ID AND
mic.organization_id = p_inventory_org_id
)
)
AND ( p_catalog_category_id IS NULL OR
p_catalog_category_id IN
( SELECT micgk.item_catalog_group_id
FROM mtl_item_catalog_groups_kfv micgk
WHERE micgk.item_catalog_group_id = CLN_ITEMMST_ITEMHEADER_V.item_catalog_group_id
)
)
AND ( p_item_status IS NULL OR
INVENTORY_ITEM_STATUS_CODE = p_item_status)
AND (
CONCATENATED_SEGMENTS >= nvl(p_from_items,CONCATENATED_SEGMENTS)
AND
CONCATENATED_SEGMENTS <= nvl(p_to_items,CONCATENATED_SEGMENTS)
)
ORDER BY concatenated_segments;
select eth.party_type, eth.party_id, eth.party_site_id
INTO l_tr_partner_type, l_tr_partner_id, l_tr_partner_site
from ecx_tp_headers eth
where eth.tp_header_id = p_tp_header_id;
SELECT 'x' into l_dummy_check FROM dual
WHERE EXISTS (SELECT 'X' FROM mtl_system_items_b_kfv msib, --mtl_system_items_vl msib,
mtl_customer_item_xrefs mcix, -- mtl_item_revisions mir,
po_hazard_classes_tl phct,
MTL_CUSTOMER_ITEMS MCI,
HZ_PARTIES HZP,
MFG_LOOKUPS MFL ,
HZ_CUST_ACCOUNTS HZC,
AR_LOOKUPS ARL
WHERE mcix.customer_item_id = mci.customer_item_id AND
mcix.inventory_item_id(+)= msib.inventory_item_id AND
mcix.master_organization_id =msib.organization_id AND
mci.customer_id(+) = cln_syncitem_pkg.get_cust_acct_id () AND
-- msib.inventory_item_id = mir.inventory_item_id(+) AND
-- msib.organization_id = mir.organization_id(+) AND
MCI.CUSTOMER_CATEGORY_CODE = ARL.LOOKUP_CODE(+) AND
msib.service_item_flag = 'N' AND
msib.inventory_item_flag = 'Y' AND
msib.customer_order_enabled_flag = 'Y' AND
MCI.INACTIVE_FLAG = 'N' AND
HZC.PARTY_ID = HZP.PARTY_ID AND HZC.STATUS = 'A' AND
msib.hazard_class_id = phct.hazard_class_id(+) AND
MCI.CUSTOMER_ID = HZC.CUST_ACCOUNT_ID AND
MCI.ITEM_DEFINITION_LEVEL = MFL.LOOKUP_CODE AND
MFL.LOOKUP_TYPE = 'INV_ITEM_DEFINITION_LEVEL' AND
--mir.revision = (SELECT MAX (revision) FROM mtl_item_revisions WHERE inventory_item_id = mir.inventory_item_id AND
--organization_id = mir.organization_id) AND
phct.LANGUAGE(+) = USERENV ('lang') AND
ARL.ENABLED_FLAG(+) = 'Y' AND
ARL.LOOKUP_TYPE(+) = 'ADDRESS_CATEGORY' AND
TRUNC(SYSDATE) BETWEEN NVL(TRUNC((ARL.START_DATE_ACTIVE(+))),SYSDATE) AND
NVL(TRUNC((ARL.END_DATE_ACTIVE(+))), SYSDATE) AND
( ( NVL (fnd_profile.VALUE ('CLN_ITEM_SEND_CUST_XREF_ONLY'),'N') = 'Y' AND mci.customer_item_number IS NOT NULL ) OR
NVL (fnd_profile.VALUE ('CLN_ITEM_SEND_CUST_XREF_ONLY'), 'N') ='N' ) AND
( p_category_set_id IS NULL OR p_category_set_id IN
( SELECT mcsvc.category_set_id
FROM mtl_item_categories mic,
mtl_category_set_valid_cats mcsvc
WHERE mcsvc.category_set_id = mic.category_set_id AND
mic.inventory_item_id = msib.INVENTORY_ITEM_ID AND
mic.organization_id = p_inventory_org_id )
) AND
( p_category_id IS NULL OR p_category_id IN
( SELECT mic.category_id
FROM mtl_item_categories mic
WHERE mic.category_id = p_category_id AND
mic.inventory_item_id = msib.INVENTORY_ITEM_ID AND
mic.organization_id = p_inventory_org_id )
)AND
( p_catalog_category_id IS NULL OR p_catalog_category_id IN
( SELECT micgk.item_catalog_group_id
FROM mtl_item_catalog_groups_kfv micgk
WHERE micgk.item_catalog_group_id = msib.item_catalog_group_id)
)AND
( p_item_status IS NULL OR msib.INVENTORY_ITEM_STATUS_CODE = p_item_status)AND
( msib.CONCATENATED_SEGMENTS >= NVL(l_from_items,msib.CONCATENATED_SEGMENTS) AND
msib.CONCATENATED_SEGMENTS <= NVL(l_to_items,msib.CONCATENATED_SEGMENTS)));
FND_MESSAGE.SET_NAME('CLN','CLN_CH_NO_ROW_SELECTED');
SELECT sysdate into l_date from dual;
SELECT CLN_SYNCITEM_S.nextval into l_syncitem_seq from dual;
SELECT CLN_SYNCITEM_S.nextval INTO l_syncitem_seq FROM dual;
PROCEDURE Send_Syncitem_Delete(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out NOCOPY varchar2) IS
l_debug_level NUMBER;
select eth.tp_header_id
from ecx_tp_headers eth, ecx_tp_details etd, ecx_ext_processes eep,
ecx_transactions et, hz_parties hp, hz_party_sites hps, hz_locations hl
where eth.tp_header_id = etd.tp_header_id
and etd.EXT_PROCESS_ID = eep.EXT_PROCESS_ID and eth.party_id = hp.party_id
and eth.party_site_id = hps.party_site_id and hps.location_id = hl.location_id
and eep.transaction_id = et.transaction_id and et.transaction_type = 'CLN'
and et.transaction_subtype = 'SYNCITEMDELO' and eep.direction = 'OUT';
x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : Entered Procedure';
select FND_PROFILE.VALUE('ORG_ID')
into l_organization_id
from dual;
select eth.party_id, eth.party_site_id
into party_id, party_site_id
from ecx_tp_headers eth
where eth.tp_header_id = l_tp_header_id;
x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : Initialized procedure parameters';
x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : XML Setup Check Done';
x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : XML Setup does not exist';
x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : XML Setup exists';
SELECT CLN_SYNCITEM_S.nextval into l_syncitem_seq from dual;
SELECT sysdate into l_date from dual;
x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : Unique key created';
x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : Create Event Parameters Setup';
x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : Create Event Raised';
x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : Initialize Send Document Parameters';
x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : Send Document Event Raised';
x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : Exiting Procedure';
x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : ERROR';
END Send_Syncitem_Delete;
PROCEDURE Archive_Deleted_Items(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out NOCOPY varchar2) IS
l_debug_level NUMBER;
CURSOR c_DeletedItems
IS
SELECT distinct msib.concatenated_segments, msib.item_type, msib.primary_uom_code, mci.customer_item_number
FROM mtl_system_items_b_kfv msib, mtl_system_items_tl msit, mtl_customer_item_xrefs mcix, mtl_customer_items mci,
mtl_item_revisions mir, mtl_item_catalog_groups_kfv micgk, po_hazard_classes_tl phct
WHERE msib.inventory_item_id = msit.inventory_item_id(+) and msib.inventory_item_id = mcix.inventory_item_id(+)
and mcix.customer_item_id = mci.customer_item_id(+) and msib.inventory_item_id = mir.inventory_item_id(+)
and msit.organization_id = msib.organization_id and mir.organization_id = msib.organization_id
and msib.service_item_flag = 'N' and msib.inventory_item_flag = 'Y'
and msib.item_catalog_group_id = micgk.item_catalog_group_id(+) and msib.hazard_class_id = phct.hazard_class_id(+)
and msib.inventory_item_id = p_inventory_item_id and msib.organization_id = p_org_id;
x_progress := 'CLN_SYNCITEM_PKG.Archive_Deleted_Items : Entered Procedure';
FOR del_rec in c_DeletedItems LOOP
--table to hold the records of the deleted item, index being (inventory_item_id, customer_item_number)
INSERT INTO cln_itemmst_deleted_items
(inventory_item_id, organization_id, concatenated_segments, item_type, primary_uom_code, customer_item_number)
VALUES
(p_inventory_item_id, p_org_id, del_rec.concatenated_segments, del_rec.item_type, del_rec.primary_uom_code, del_rec.customer_item_number);
x_progress := 'CLN_SYNCITEM_PKG.Archive_Deleted_Items : Exiting Procedure';
x_progress := 'CLN_SYNCITEM_PKG.Archive_Deleted_Items : ERROR';
END Archive_Deleted_Items;
PROCEDURE Delete_Archived_Items(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out NOCOPY varchar2) IS
l_debug_level NUMBER;
x_progress := 'CLN_SYNCITEM_PKG.Delete_Archived_Items : Entered Procedure';
DELETE FROM cln_itemmst_deleted_items
WHERE inventory_item_id = p_inventory_item_id AND organization_id = p_org_id;
x_progress := 'CLN_SYNCITEM_PKG.Delete_Archived_Items : Exiting Procedure';
x_progress := 'CLN_SYNCITEM_PKG.Delete_Archived_Items : ERROR';
END Delete_Archived_Items;