[Home] [Help]
1: PACKAGE BODY ICX_CAT_R12_UPGRADE_PVT AS
2: /* $Header: ICXV12UB.pls 120.24.12010000.2 2008/08/02 14:38:35 kkram ship $*/
3:
4: -----------------------------------------------------------
5: -- Global variables --
4: -----------------------------------------------------------
5: -- Global variables --
6: -----------------------------------------------------------
7: -- Constants
8: G_PKG_NAME CONSTANT VARCHAR2(30) := 'ICX_CAT_R12_UPGRADE_PVT';
9: g_GBPA_hdr_count PLS_INTEGER := 0;
10: g_total_row_count PLS_INTEGER := 0;
11: g_PDOI_batch_id NUMBER;
12: g_interface_source_code po_headers_interface.interface_source_code%TYPE := 'CATALOG R12 UPGRADE';
86: TYPE g_r12_upg_rec_type IS RECORD
87: (
88: rt_item_id NUMBER,
89: supplier_site_id NUMBER,
90: currency icx_cat_r12_upgrade.currency%TYPE,
91: price_contract_id NUMBER,
92: src_contract_id NUMBER,
93: cpa_reference NUMBER,
94: po_category_id icx_cat_r12_upgrade.po_category_id%TYPE,
90: currency icx_cat_r12_upgrade.currency%TYPE,
91: price_contract_id NUMBER,
92: src_contract_id NUMBER,
93: cpa_reference NUMBER,
94: po_category_id icx_cat_r12_upgrade.po_category_id%TYPE,
95: po_interface_header_id NUMBER,
96: po_interface_line_id NUMBER,
97: po_header_id NUMBER,
98: po_line_id NUMBER,
95: po_interface_header_id NUMBER,
96: po_interface_line_id NUMBER,
97: po_header_id NUMBER,
98: po_line_id NUMBER,
99: created_language icx_cat_r12_upgrade.created_language%TYPE,
100: -- TBD extractor_updated_flag icx_cat_r12_upgrade.extractor_updated_flag%TYPE,
101: old_po_interface_line_id NUMBER
102: );
103:
96: po_interface_line_id NUMBER,
97: po_header_id NUMBER,
98: po_line_id NUMBER,
99: created_language icx_cat_r12_upgrade.created_language%TYPE,
100: -- TBD extractor_updated_flag icx_cat_r12_upgrade.extractor_updated_flag%TYPE,
101: old_po_interface_line_id NUMBER
102: );
103:
104: g_po_hdrs_int_rec g_po_hdrs_int_rec_type;
181: gIAVTRtItemIdTbl DBMS_SQL.NUMBER_TABLE;
182: gIAVTLanguageTbl DBMS_SQL.VARCHAR2_TABLE;
183: gIAVTCheckDescUpdateTbl DBMS_SQL.VARCHAR2_TABLE;
184:
185: --INSERT icx_cat_r12_upgrade
186: gIRURtItemIdTbl DBMS_SQL.NUMBER_TABLE;
187: gIRUSupplierSiteIdTbl DBMS_SQL.NUMBER_TABLE;
188: gIRUCurrencyTbl DBMS_SQL.VARCHAR2_TABLE;
189: gIRUCpaReferenceTbl DBMS_SQL.NUMBER_TABLE;
194: gIRUPoInterfaceLineIdTbl DBMS_SQL.NUMBER_TABLE;
195: gIRUCreatedLanguageTbl DBMS_SQL.VARCHAR2_TABLE;
196: -- TBD gIRUExtractorUpdatedFlagTbl DBMS_SQL.VARCHAR2_TABLE;
197:
198: --UPDATE icx_cat_r12_upgrade
199: gURURtItemIdTbl DBMS_SQL.NUMBER_TABLE;
200: gURUPoCategoryIdTbl DBMS_SQL.NUMBER_TABLE;
201: gURUCpaReferenceTbl DBMS_SQL.NUMBER_TABLE;
202: gURUPriceContractIdTbl DBMS_SQL.NUMBER_TABLE;
207: gURUPoHeaderIdTbl DBMS_SQL.NUMBER_TABLE;
208: gURUPoLineIdTbl DBMS_SQL.NUMBER_TABLE;
209: gURUCreatedLanguageTbl DBMS_SQL.VARCHAR2_TABLE;
210:
211: --DELETE icx_cat_r12_upgrade
212: gDRURtItemIdTbl DBMS_SQL.NUMBER_TABLE;
213: gDRUPoInterfaceHeaderIdTbl DBMS_SQL.NUMBER_TABLE;
214: gDRUPoInterfaceLineIdTbl DBMS_SQL.NUMBER_TABLE;
215:
299: gIAVTCheckDescUpdateTbl.DELETE;
300: END IF;
301:
302: IF (p_action_mode IN ('ALL', 'INSERT_R12_UPGRADE')) THEN
303: --INSERT icx_cat_r12_upgrade
304: gIRURtItemIdTbl.DELETE;
305: gIRUSupplierSiteIdTbl.DELETE;
306: gIRUCurrencyTbl.DELETE;
307: gIRUCpaReferenceTbl.DELETE;
314: -- TBD gIRUExtractorUpdatedFlagTbl.DELETE;
315: END IF;
316:
317: IF (p_action_mode IN ('ALL', 'UPDATE_R12_UPGRADE')) THEN
318: --UPDATE icx_cat_r12_upgrade
319: gURURtItemIdTbl.DELETE;
320: gURUPoCategoryIdTbl.DELETE;
321: gURUCpaReferenceTbl.DELETE;
322: gURUPriceContractIdTbl.DELETE;
329: gURUCreatedLanguageTbl.DELETE;
330: END IF;
331:
332: IF (p_action_mode IN ('ALL', 'DELETE_R12_UPGRADE')) THEN
333: --DELETE icx_cat_r12_upgrade
334: gDRURtItemIdTbl.DELETE;
335: gDRUPoInterfaceHeaderIdTbl.DELETE;
336: gDRUPoInterfaceLineIdTbl.DELETE;
337: END IF;
480: ICX_CAT_UTIL_PVT.getTableElement(gIAVTCheckDescUpdateTbl, p_index) || ', ';
481: END IF;
482:
483: IF (p_action_mode = 'INSERT_R12_UPGRADE') THEN
484: --INSERT icx_cat_r12_upgrade
485: l_string := l_string || ' gIRURtItemIdTbl: ' ||
486: ICX_CAT_UTIL_PVT.getTableElement(gIRURtItemIdTbl, p_index) || ', ';
487: l_string := l_string || ' gIRUSupplierSiteIdTbl: ' ||
488: ICX_CAT_UTIL_PVT.getTableElement(gIRUSupplierSiteIdTbl, p_index) || ', ';
506: -- TBD ICX_CAT_UTIL_PVT.getTableElement(gIRUExtractorUpdatedFlagTbl, p_index) || ', ';
507: END IF;
508:
509: IF (p_action_mode = 'UPDATE_R12_UPGRADE') THEN
510: --UPDATE icx_cat_r12_upgrade
511: l_string := l_string || ' gURURtItemIdTbl: ' ||
512: ICX_CAT_UTIL_PVT.getTableElement(gURURtItemIdTbl, p_index) || ', ';
513: l_string := l_string || ' gURUPoCategoryIdTbl: ' ||
514: ICX_CAT_UTIL_PVT.getTableElement(gURUPoCategoryIdTbl, p_index) || ', ';
532: ICX_CAT_UTIL_PVT.getTableElement(gURUCreatedLanguageTbl, p_index) || ', ';
533: END IF;
534:
535: IF (p_action_mode = 'DELETE_R12_UPGRADE') THEN
536: --DELETE icx_cat_r12_upgrade
537: l_string := l_string || ' gDRURtItemIdTbl: ' ||
538: ICX_CAT_UTIL_PVT.getTableElement(gDRURtItemIdTbl, p_index) || ', ';
539: l_string := l_string || ' gDRUPoInterfaceHeaderIdTbl: ' ||
540: ICX_CAT_UTIL_PVT.getTableElement(gDRUPoInterfaceHeaderIdTbl, p_index) || ', ';
826: l_err_loc := 1200;
827:
828: IF (p_action_mode = 'INSERT_R12_UPGRADE') THEN
829: l_err_loc := 1300;
830: -- INSERT icx_cat_r12_upgrade
831: l_log_string := ' gIRURtItemIdTbl: ' ||
832: ICX_CAT_UTIL_PVT.getTableElement(gIRURtItemIdTbl, p_index) || '; ';
833: FND_LOG.string(p_log_level, l_module_name, l_log_string);
834:
873: l_err_loc := 1400;
874:
875: IF (p_action_mode = 'UPDATE_R12_UPGRADE') THEN
876: l_err_loc := 1500;
877: -- UPDATE icx_cat_r12_upgrade
878: l_log_string := ' gURURtItemIdTbl: ' ||
879: ICX_CAT_UTIL_PVT.getTableElement(gURURtItemIdTbl, p_index) || '; ';
880: FND_LOG.string(p_log_level, l_module_name, l_log_string);
881:
924: l_err_loc := 1600;
925:
926: IF (p_action_mode = 'DELETE_R12_UPGRADE') THEN
927: l_err_loc := 1700;
928: -- DELETE icx_cat_r12_upgrade
929: l_log_string := ' gDRURtItemIdTbl: ' ||
930: ICX_CAT_UTIL_PVT.getTableElement(gDRURtItemIdTbl, p_index) || '; ';
931: FND_LOG.string(p_log_level, l_module_name, l_log_string);
932:
1811:
1812: l_err_loc := 260;
1813: l_action_mode := 'INSERT_R12_UPGRADE';
1814: FORALL i in 1..gIRURtItemIdTbl.COUNT
1815: INSERT INTO icx_cat_r12_upgrade
1816: (supplier_site_id, currency, cpa_reference,
1817: price_contract_id, src_contract_id, po_category_id,
1818: rt_item_id, po_interface_header_id, po_interface_line_id,
1819: created_language,
1838: IF (gIRURtItemIdTbl.COUNT > 0) THEN
1839: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1840: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1841: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1842: 'No: of rows inserted into icx_cat_r12_upgrade:' ||SQL%ROWCOUNT);
1843: END IF;
1844: END IF;
1845:
1846: l_err_loc := 280;
1848:
1849: l_err_loc := 290;
1850: l_action_mode := 'UPDATE_R12_UPGRADE';
1851: FORALL i in 1..gURURtItemIdTbl.COUNT
1852: UPDATE icx_cat_r12_upgrade
1853: SET po_interface_header_id = gURUPoInterfaceHeaderIdTbl(i),
1854: po_interface_line_id = gURUPoInterfaceLineIdTbl(i),
1855: po_category_id = gURUPoCategoryIdTbl(i),
1856: cpa_reference = gURUCpaReferenceTbl(i),
1874: IF (gURURtItemIdTbl.COUNT > 0) THEN
1875: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1876: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1877: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1878: 'No: of rows updated in icx_cat_r12_upgrade:' ||SQL%ROWCOUNT);
1879: END IF;
1880: END IF;
1881:
1882: l_err_loc := 310;
1884:
1885: l_err_loc := 320;
1886: l_action_mode := 'DELETE_R12_UPGRADE';
1887: FORALL i in 1..gDRURtItemIdTbl.COUNT
1888: DELETE FROM icx_cat_r12_upgrade
1889: WHERE rt_item_id = gDRURtItemIdTbl(i)
1890: AND po_interface_header_id = gDRUPoInterfaceHeaderIdTbl(i)
1891: AND po_interface_line_id = gDRUPoInterfaceLineIdTbl(i);
1892:
1894: IF (gDRURtItemIdTbl.COUNT > 0) THEN
1895: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1896: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1897: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1898: 'No: of rows deleted from icx_cat_r12_upgrade:' ||SQL%ROWCOUNT);
1899: END IF;
1900: END IF;
1901:
1902: l_err_loc := 340;
2973: icx_cat_items_tlp itemtlp,
2974: icx_por_category_order_map map,
2975: icx_cat_items_b itemb,
2976: icx_cat_ext_items_tlp extitemtlp,
2977: icx_cat_r12_upgrade upg
2978: WHERE price.price_type = 'BULKLOAD'
2979: AND price.rt_item_id = itemtlp.rt_item_id
2980: AND NOT EXISTS (SELECT 'extracted price'
2981: FROM icx_cat_item_prices priceIn
3163: l_document_header_id := ICX_CAT_UTIL_PVT.g_NULL_NUMBER;
3164: END IF;
3165: ELSE
3166: l_err_loc := 240;
3167: -- Just insert into the icx_cat_r12_upgrade_autosource
3168: l_document_header_id := l_contract_id_tbl(i);
3169: END IF;
3170:
3171: l_err_loc := 270;
3249: END contractAutoSourcing;
3250:
3251: PROCEDURE checkAndProcessGBPALines
3252: IS
3253: -- outer join with icx_cat_r12_upgrade, on rt_item_id (i.e. org_id and supplier_id),
3254: -- supplier_site_id, currency and contract_id (pricing attributes)
3255: -- join with contract_id needed because the items with the same combination of the above
3256: -- could exist in two contracts.
3257: -- outer join needed: because we need to find the items that were already processed in
3272: upg.po_interface_header_id, upg.created_language, upg.po_interface_line_id,
3273: price.price_type, itemtlp.primary_category_name
3274: FROM icx_cat_item_prices price, icx_cat_items_tlp itemtlp, icx_cat_items_b itemsb,
3275: icx_por_category_order_map map, icx_cat_r12_upg_autosource src,
3276: icx_cat_r12_upgrade upg
3277: WHERE price.price_type = 'BULKLOAD'
3278: AND price.rt_item_id = itemtlp.rt_item_id
3279: AND price.rt_item_id = itemsb.rt_item_id
3280: AND NOT EXISTS (SELECT 'extracted price'
3308: SUBSTRB(itemtlp.description, 1, 240) description, itemsb.catalog_name,
3309: upg.po_interface_header_id, upg.created_language, upg.po_interface_line_id,
3310: price.price_type, itemtlp.primary_category_name
3311: FROM icx_cat_item_prices price, icx_cat_items_tlp itemtlp, icx_cat_items_b itemsb,
3312: icx_por_category_order_map map, icx_cat_r12_upgrade upg
3313: WHERE price.price_type = 'CONTRACT'
3314: AND price.rt_item_id = itemtlp.rt_item_id
3315: AND price.rt_item_id = itemsb.rt_item_id
3316: AND NOT EXISTS (SELECT 'extracted price'
3548: l_err_loc := 284;
3549: g_r12_upg_rec.rt_item_id := l_rt_item_id_tbl(i);
3550: g_r12_upg_rec.supplier_site_id := g_current_gbpa_hdr_rec.vendor_site_id;
3551: g_r12_upg_rec.currency := g_current_gbpa_hdr_rec.currency_code;
3552: -- icx_cat_r12_upgrade.price_contract_id is same as the contract_id in price table
3553: -- icx_cat_r12_upgrade.src_contract_id is same as the contract_id returned from autosource
3554: -- Need both contract_ids to figure out any changes in the source document during delta processing.
3555: l_err_loc := 285;
3556: IF (l_price_type_tbl(i) = 'BULKLOAD') THEN
3549: g_r12_upg_rec.rt_item_id := l_rt_item_id_tbl(i);
3550: g_r12_upg_rec.supplier_site_id := g_current_gbpa_hdr_rec.vendor_site_id;
3551: g_r12_upg_rec.currency := g_current_gbpa_hdr_rec.currency_code;
3552: -- icx_cat_r12_upgrade.price_contract_id is same as the contract_id in price table
3553: -- icx_cat_r12_upgrade.src_contract_id is same as the contract_id returned from autosource
3554: -- Need both contract_ids to figure out any changes in the source document during delta processing.
3555: l_err_loc := 285;
3556: IF (l_price_type_tbl(i) = 'BULKLOAD') THEN
3557: g_r12_upg_rec.price_contract_id := -2;
3949: l_err_loc := 244;
3950: g_r12_upg_rec.rt_item_id := l_rt_item_id_tbl(i);
3951: g_r12_upg_rec.supplier_site_id := g_current_gbpa_hdr_rec.vendor_site_id;
3952: g_r12_upg_rec.currency := g_current_gbpa_hdr_rec.currency_code;
3953: -- icx_cat_r12_upgrade.price_contract_id is same as the contract_id in price table
3954: -- icx_cat_r12_upgrade.src_contract_id is same as the contract_id returned from autosource
3955: -- Need both contract_ids to figure out any changes in the source document during delta processing.
3956: l_err_loc := 246;
3957: IF (l_price_type_tbl(i) = 'BULKLOAD') THEN
3950: g_r12_upg_rec.rt_item_id := l_rt_item_id_tbl(i);
3951: g_r12_upg_rec.supplier_site_id := g_current_gbpa_hdr_rec.vendor_site_id;
3952: g_r12_upg_rec.currency := g_current_gbpa_hdr_rec.currency_code;
3953: -- icx_cat_r12_upgrade.price_contract_id is same as the contract_id in price table
3954: -- icx_cat_r12_upgrade.src_contract_id is same as the contract_id returned from autosource
3955: -- Need both contract_ids to figure out any changes in the source document during delta processing.
3956: l_err_loc := 246;
3957: IF (l_price_type_tbl(i) = 'BULKLOAD') THEN
3958: g_r12_upg_rec.price_contract_id := -2;
4319: l_po_header_id NUMBER;
4320: BEGIN
4321: SELECT po_interface_header_id, po_header_id
4322: INTO l_po_interface_header_id, l_po_header_id
4323: FROM icx_cat_r12_upgrade upg, icx_cat_items_b b
4324: WHERE b.rt_item_id = upg.rt_item_id
4325: AND b.org_id = p_org_id
4326: AND b.supplier_id = p_supplier_id
4327: AND upg.supplier_site_id = p_supplier_site_id
4363: price.price_type, itemtlp.primary_category_name
4364: FROM icx_cat_r12_upg_autosource src, icx_cat_item_prices price,
4365: icx_cat_items_tlp itemtlp, icx_por_category_order_map map,
4366: icx_cat_items_b itemb, icx_cat_ext_items_tlp extitemtlp,
4367: icx_cat_r12_upgrade upg, po_attribute_values_tlp attr
4368: WHERE price.price_type = 'BULKLOAD'
4369: AND price.rt_item_id = itemtlp.rt_item_id
4370: AND NOT EXISTS (SELECT 'extracted price'
4371: FROM icx_cat_item_prices priceIn
4420: price.price_type, itemtlp.primary_category_name
4421: FROM icx_cat_item_prices price, icx_cat_items_tlp itemtlp,
4422: icx_por_category_order_map map,
4423: icx_cat_items_b itemb, icx_cat_ext_items_tlp extitemtlp,
4424: icx_cat_r12_upgrade upg, po_attribute_values_tlp attr
4425: WHERE price.price_type = 'CONTRACT'
4426: AND price.rt_item_id = itemtlp.rt_item_id
4427: AND NOT EXISTS (SELECT 'extracted price'
4428: FROM icx_cat_item_prices priceIn
4637: THEN
4638: -- i.e. both the interface_header_id from the lines cursor and from the headers cursors are the same
4639: -- We need to insert rows in po_headers_interface, po_lines_interface, po_attr_values_interface
4640: -- and po_attr_values_tlp_interface
4641: -- IF interface_header_id from the headers cursor is null then insert into icx_cat_r12_upgrade table
4642: -- IF interface_header_id from the headers cursor is not null then update icx_cat_r12_upgrade table
4643: IF (l_firstRow) THEN
4644: --Create the header record only once for the
4645: --current combination of header attributes
4638: -- i.e. both the interface_header_id from the lines cursor and from the headers cursors are the same
4639: -- We need to insert rows in po_headers_interface, po_lines_interface, po_attr_values_interface
4640: -- and po_attr_values_tlp_interface
4641: -- IF interface_header_id from the headers cursor is null then insert into icx_cat_r12_upgrade table
4642: -- IF interface_header_id from the headers cursor is not null then update icx_cat_r12_upgrade table
4643: IF (l_firstRow) THEN
4644: --Create the header record only once for the
4645: --current combination of header attributes
4646: l_err_loc := 212;
4839: l_dml_reqd_in_lines := TRUE;
4840: ELSE
4841: l_dml_reqd_in_lines := FALSE;
4842: END IF;
4843: -- Figure out if the row needs to be inserted / updated in icx_cat_r12_upgrade table
4844: l_dml_reqd_in_r12Upgrade := TRUE;
4845: IF (l_po_interface_line_id_tbl(i) IS NULL) THEN
4846: l_createR12UpgradeRow := TRUE;
4847: ELSE
4869:
4870: IF (l_dml_reqd_in_r12Upgrade) THEN
4871: l_interface_line_id := getNextInterfaceLineIdFromSeq;
4872: -- Put the current values into g_r12_upg_rec for
4873: -- icx_cat_r12_upgrade (insert/update will be depending on l_createR12UpgradeRow)
4874: l_err_loc := 244;
4875: g_r12_upg_rec.rt_item_id := l_rt_item_id_tbl(i);
4876: g_r12_upg_rec.supplier_site_id := g_current_gbpa_hdr_rec.vendor_site_id;
4877: g_r12_upg_rec.currency := g_current_gbpa_hdr_rec.currency_code;
4874: l_err_loc := 244;
4875: g_r12_upg_rec.rt_item_id := l_rt_item_id_tbl(i);
4876: g_r12_upg_rec.supplier_site_id := g_current_gbpa_hdr_rec.vendor_site_id;
4877: g_r12_upg_rec.currency := g_current_gbpa_hdr_rec.currency_code;
4878: -- icx_cat_r12_upgrade.price_contract_id is same as the contract_id in price table
4879: -- icx_cat_r12_upgrade.src_contract_id is same as the contract_id returned from autosource
4880: -- Need both contract_ids to figure out any changes in the source document during delta processing.
4881: l_err_loc := 246;
4882: IF (l_price_type_tbl(i) = 'BULKLOAD') THEN
4875: g_r12_upg_rec.rt_item_id := l_rt_item_id_tbl(i);
4876: g_r12_upg_rec.supplier_site_id := g_current_gbpa_hdr_rec.vendor_site_id;
4877: g_r12_upg_rec.currency := g_current_gbpa_hdr_rec.currency_code;
4878: -- icx_cat_r12_upgrade.price_contract_id is same as the contract_id in price table
4879: -- icx_cat_r12_upgrade.src_contract_id is same as the contract_id returned from autosource
4880: -- Need both contract_ids to figure out any changes in the source document during delta processing.
4881: l_err_loc := 246;
4882: IF (l_price_type_tbl(i) = 'BULKLOAD') THEN
4883: g_r12_upg_rec.price_contract_id := -2;
4934: ', g_current_gbpa_hdr_rec.po_header_id:' || g_current_gbpa_hdr_rec.po_header_id ||
4935: ', g_current_gbpa_hdr_rec.upg_created_language:' || g_current_gbpa_hdr_rec.upg_created_language );
4936: END IF;
4937: --update the po_interface_header_id and po_interface_line_id
4938: --in icx_cat_r12_upgrade with the new ones.
4939: l_err_loc := 258;
4940: updateR12Upgrade;
4941: END IF;
4942: ELSE
5166: nvl(upg.cpa_reference, -2) upg_cpa_reference
5167: FROM icx_cat_r12_upg_autosource src, icx_cat_item_prices price,
5168: icx_cat_items_tlp itemtlp, icx_por_category_order_map map,
5169: icx_cat_items_b itemb, icx_cat_ext_items_tlp extitemtlp,
5170: icx_cat_r12_upgrade upg
5171: WHERE price.price_type = 'BULKLOAD'
5172: AND price.rt_item_id = itemtlp.rt_item_id
5173: AND NOT EXISTS (SELECT 'extracted price'
5174: FROM icx_cat_item_prices priceIn
5206: upg.po_header_id, upg.created_language,
5207: nvl(upg.cpa_reference, -2) upg_cpa_reference
5208: FROM icx_cat_item_prices price, icx_cat_items_tlp itemtlp,
5209: icx_cat_items_b itemb, icx_cat_ext_items_tlp extitemtlp,
5210: icx_cat_r12_upgrade upg
5211: WHERE price.price_type = 'CONTRACT'
5212: AND price.rt_item_id = itemtlp.rt_item_id
5213: AND NOT EXISTS (SELECT 'extracted price'
5214: FROM icx_cat_item_prices priceIn
5359: -- catalog that was never migrated due to errors, so they will have po_header_id as null
5360: CURSOR getDeletedItemPricesInCatlgCsr IS
5361: SELECT rt_item_id, po_interface_header_id, po_interface_line_id,
5362: upg.po_header_id, po_line_id, ph.org_id
5363: FROM icx_cat_r12_upgrade upg, po_headers_all ph
5364: WHERE NOT EXISTS (SELECT 'x'
5365: FROM icx_cat_items_b itemsB, icx_cat_item_prices prices
5366: WHERE itemsB.rt_item_id = prices.rt_item_id
5367: AND upg.rt_item_id = prices.rt_item_id
5464:
5465: IF (l_po_line_id_tbl(i) IS NOT NULL) THEN
5466: --Only if the line was created as a po_line, need to Insert a row
5467: --in po_lines_interface with DELETE action
5468: --And also delete the row from icx_cat_r12_upgrade.
5469: IF (l_prev_po_header_id <> l_po_header_id_tbl(i)) THEN
5470: --po_header_id has changed, so get the next interfaceHeaderId
5471: --and populate a line in po_headers_interface
5472: l_interface_header_id := getNextInterfaceHdrIdFromSeq;
5599: IS
5600: CURSOR syncPOIntHdrIdInR12UpgTblCsr IS
5601: SELECT doc.po_header_id, COUNT(*)
5602: FROM (SELECT po_interface_header_id, po_header_id
5603: FROM icx_cat_r12_upgrade
5604: WHERE po_header_id IS NOT NULL
5605: GROUP BY po_interface_header_id, po_header_id) doc
5606: GROUP BY po_header_id
5607: HAVING COUNT(*) > 1;
5607: HAVING COUNT(*) > 1;
5608:
5609: CURSOR syncRowsInR12UpgTblCsr IS
5610: SELECT rowid
5611: FROM icx_cat_r12_upgrade
5612: WHERE po_header_id IS NULL
5613: AND po_interface_header_id IS NOT NULL;
5614:
5615: ----- Start of declaring columns selected in the cursor -----
5670: END LOOP; --FOR LOOP of l_po_header_id_tbl
5671:
5672: l_err_loc := 800;
5673: FORALL i IN 1..l_upo_hdr_id_tbl.COUNT
5674: UPDATE icx_cat_r12_upgrade
5675: SET po_interface_header_id = l_upo_int_hdr_id_tbl(i)
5676: WHERE po_header_id = l_upo_hdr_id_tbl(i);
5677:
5678: l_err_loc := 900;
5717: l_item_row_count := l_item_row_count + l_rowid_tbl.COUNT;
5718:
5719: l_err_loc := 1600;
5720: FORALL i IN 1..l_rowid_tbl.COUNT
5721: UPDATE icx_cat_r12_upgrade
5722: SET po_interface_header_id = NULL,
5723: created_language = NULL
5724: WHERE rowid = l_rowid_tbl(i);
5725:
5727: IF (l_rowid_tbl.COUNT > 0) THEN
5728: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5729: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
5730: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
5731: 'No: of rows updated in icx_cat_r12_upgrade:' ||SQL%ROWCOUNT);
5732: END IF;
5733: END IF;
5734:
5735: l_err_loc := 1800;
5785: ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start...');
5786: END IF;
5787: /*
5788: Possible changes after pre-upgrade and before another run of pre-upgrade/upgrade
5789: 1. Check for items added i.e. rt_item_id not in icx_cat_r12_upgrade
5790: 2. Check for price added i.e. price_rowid not in icx_cat_r12_upgrade
5791: 3. Check for item updated/translation added
5792: Note: Steps 1, 2 and 3 will be done together using the following approach:
5793: a) Outer join with icx_cat_r12_upgrade
5786: END IF;
5787: /*
5788: Possible changes after pre-upgrade and before another run of pre-upgrade/upgrade
5789: 1. Check for items added i.e. rt_item_id not in icx_cat_r12_upgrade
5790: 2. Check for price added i.e. price_rowid not in icx_cat_r12_upgrade
5791: 3. Check for item updated/translation added
5792: Note: Steps 1, 2 and 3 will be done together using the following approach:
5793: a) Outer join with icx_cat_r12_upgrade
5794: b) Check for last_update_date > g_bulk_last_run_date in icx_cat_items_b, icx_cat_items_tlp,
5789: 1. Check for items added i.e. rt_item_id not in icx_cat_r12_upgrade
5790: 2. Check for price added i.e. price_rowid not in icx_cat_r12_upgrade
5791: 3. Check for item updated/translation added
5792: Note: Steps 1, 2 and 3 will be done together using the following approach:
5793: a) Outer join with icx_cat_r12_upgrade
5794: b) Check for last_update_date > g_bulk_last_run_date in icx_cat_items_b, icx_cat_items_tlp,
5795: icx_cat_ext_items_tlp and icx_cat_item_prices
5796: 4. Check for items deleted i.e. rt_item_id exists in icx_cat_r12_upgrade but not in icx_cat_items_b
5797: 5. Check for price deleted i.e. price_rowid exists in icx_cat_r12_upgrade but not in icx_cat_items_b
5792: Note: Steps 1, 2 and 3 will be done together using the following approach:
5793: a) Outer join with icx_cat_r12_upgrade
5794: b) Check for last_update_date > g_bulk_last_run_date in icx_cat_items_b, icx_cat_items_tlp,
5795: icx_cat_ext_items_tlp and icx_cat_item_prices
5796: 4. Check for items deleted i.e. rt_item_id exists in icx_cat_r12_upgrade but not in icx_cat_items_b
5797: 5. Check for price deleted i.e. price_rowid exists in icx_cat_r12_upgrade but not in icx_cat_items_b
5798: 6. Check for any pricing hdr attribute (i.e. supplier_site_id, currency, contract_id) update after running pre-upgrade.
5799: Note: Steps 4, 5 and 6 will be done together using the following approach:
5800: a) Get all the records from icx_cat_r12_upgrade that don't exist in icx_cat_items_b and icx_cat_item_prices
5793: a) Outer join with icx_cat_r12_upgrade
5794: b) Check for last_update_date > g_bulk_last_run_date in icx_cat_items_b, icx_cat_items_tlp,
5795: icx_cat_ext_items_tlp and icx_cat_item_prices
5796: 4. Check for items deleted i.e. rt_item_id exists in icx_cat_r12_upgrade but not in icx_cat_items_b
5797: 5. Check for price deleted i.e. price_rowid exists in icx_cat_r12_upgrade but not in icx_cat_items_b
5798: 6. Check for any pricing hdr attribute (i.e. supplier_site_id, currency, contract_id) update after running pre-upgrade.
5799: Note: Steps 4, 5 and 6 will be done together using the following approach:
5800: a) Get all the records from icx_cat_r12_upgrade that don't exist in icx_cat_items_b and icx_cat_item_prices
5801: based on rt_item_id, supplier_site_id, currency, contract_id
5796: 4. Check for items deleted i.e. rt_item_id exists in icx_cat_r12_upgrade but not in icx_cat_items_b
5797: 5. Check for price deleted i.e. price_rowid exists in icx_cat_r12_upgrade but not in icx_cat_items_b
5798: 6. Check for any pricing hdr attribute (i.e. supplier_site_id, currency, contract_id) update after running pre-upgrade.
5799: Note: Steps 4, 5 and 6 will be done together using the following approach:
5800: a) Get all the records from icx_cat_r12_upgrade that don't exist in icx_cat_items_b and icx_cat_item_prices
5801: based on rt_item_id, supplier_site_id, currency, contract_id
5802: 7. Check for items that have errors i.e. the ones that were not migrated into po tables due to validation errors,
5803: These will have po_interface_header_id and po_interface_line_id populated but will have null po_header_id and po_line_id
5804: Assumptions: Translations cannot be deleted.
5803: These will have po_interface_header_id and po_interface_line_id populated but will have null po_header_id and po_line_id
5804: Assumptions: Translations cannot be deleted.
5805: */
5806: -- First we will sync up all the po_interface_header_ids
5807: -- in icx_cat_r12_upgrade which belongs to the same po_header_id but exists with
5808: -- different po_interface_header_id in icx_cat_r12_upgrade
5809: -- Reason being, lines belonging to the same po_header where processed at two different upgrade delta jobs.
5810: syncPOIntHdrIdInR12UpgTbl;
5811:
5804: Assumptions: Translations cannot be deleted.
5805: */
5806: -- First we will sync up all the po_interface_header_ids
5807: -- in icx_cat_r12_upgrade which belongs to the same po_header_id but exists with
5808: -- different po_interface_header_id in icx_cat_r12_upgrade
5809: -- Reason being, lines belonging to the same po_header where processed at two different upgrade delta jobs.
5810: syncPOIntHdrIdInR12UpgTbl;
5811:
5812: -- call the delete
5916: g_total_bulkld_row_count := 0;
5917:
5918: IF g_bulk_last_run_date IS NULL THEN
5919: l_err_loc := 230;
5920: -- Delete all the rows in icx_cat_r12_upgrade, because pre-upgrade was never run
5921: -- or never ran successfully. But icx_cat_r12_upgrade could have data from the
5922: -- previous run of data exception jobs.
5923: IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5924: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
5917:
5918: IF g_bulk_last_run_date IS NULL THEN
5919: l_err_loc := 230;
5920: -- Delete all the rows in icx_cat_r12_upgrade, because pre-upgrade was never run
5921: -- or never ran successfully. But icx_cat_r12_upgrade could have data from the
5922: -- previous run of data exception jobs.
5923: IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5924: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
5925: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
5923: IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5924: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
5925: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
5926: 'g_bulk_last_run_date:' || g_bulk_last_run_date ||
5927: '; about to truncate icx_cat_r12_upgrade...');
5928: END IF;
5929:
5930: l_err_loc := 240;
5931: l_icx_schema_name := ICX_CAT_UTIL_PVT.getIcxSchemaName;
5931: l_icx_schema_name := ICX_CAT_UTIL_PVT.getIcxSchemaName;
5932:
5933: l_err_loc := 250;
5934: EXECUTE IMMEDIATE
5935: 'TRUNCATE TABLE '|| l_icx_schema_name ||'.icx_cat_r12_upgrade';
5936:
5937: l_err_loc := 260;
5938: createGBPAsForBlkldItems; --First time
5939: ELSE
5997: l_msg_count , -- X_MSG_COUNT
5998: l_msg_data -- X_MSG_DATA
5999: );
6000:
6001: -- Remove all the rows that are created in icx_cat_r12_upgrade table in the current job.
6002: ELSE
6003: l_err_loc := 300;
6004: IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6005: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
6106: END IF;
6107:
6108: l_err_loc := 200;
6109: FORALL i IN 1..p_interface_header_id.COUNT
6110: UPDATE icx_cat_r12_upgrade upg
6111: SET (po_header_id, created_language) =
6112: (SELECT po_header_id, created_language
6113: FROM po_headers_interface phi
6114: WHERE phi.interface_header_id = upg.po_interface_header_id)
6120: l_err_loc := 350;
6121: IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6122: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
6123: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
6124: 'No: of header rows updated in icx_cat_r12_upgrade table:' || l_row_count ||
6125: ', g_job_pdoi_update_date:' || ICX_CAT_UTIL_PVT.g_job_pdoi_update_date);
6126: END IF;
6127:
6128: l_err_loc := 400;
6179: END IF;
6180:
6181: l_err_loc := 200;
6182: FORALL i IN 1..p_interface_line_id.COUNT
6183: UPDATE icx_cat_r12_upgrade upg
6184: SET po_line_id =
6185: (SELECT po_line_id
6186: FROM po_lines_interface pli
6187: WHERE pli.interface_line_id = upg.po_interface_line_id)
6193: l_err_loc := 350;
6194: IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6195: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
6196: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
6197: 'No: of line rows updated in icx_cat_r12_upgrade table:' || l_row_count ||
6198: ', g_job_pdoi_update_date:' || ICX_CAT_UTIL_PVT.g_job_pdoi_update_date);
6199: END IF;
6200:
6201: l_err_loc := 400;
6355: CURSOR updSrcDocIdsInOldFavLinesCsr IS
6356: SELECT fav.rowid, upg.po_line_id
6357: FROM por_favorite_list_lines fav,
6358: icx_cat_item_prices price,
6359: icx_cat_r12_upgrade upg
6360: WHERE fav.rt_item_id IS NOT NULL
6361: AND fav.price_list_id IS NOT NULL
6362: AND fav.source_doc_line_id IS NULL
6363: AND fav.rt_item_id = price.rt_item_id
7865: g_bpa_last_run_date,
7866: g_quote_last_run_date,
7867: g_reqtmplt_last_run_date,
7868: g_mi_last_run_date
7869: FROM icx_cat_r12_upgrade_jobs
7870: -- Check within jobs that are not data exception
7871: -- OR child data exception process ( which will be submitted from pre-upgrade program)
7872: WHERE job_type NOT IN (ICX_CAT_UTIL_PVT.g_data_exception_program,
7873: ICX_CAT_UTIL_PVT.g_child_data_excptn_program);
7958: p_data_exception_job := fnd_request.submit_request
7959: (
7960: 'ICX', -- application
7961: 'ICXCUCDER', -- program
7962: -- ICX_CAT_R12_UPGRADE_PVT.runDataExcptnRptChildProcess;
7963: null, -- description
7964: NULL, -- start_time
7965: is_sub_request, -- sub_request (TRUE from pre-upgrade, FALSE from upgrade)
7966: ICX_CAT_UTIL_PVT.g_who_columns_rec.internal_request_id, -- p_parent_int_req_id
8237: ICX_CAT_R12_DATA_EXCEP_RPT_PVT.process_data_exceptions_report(p_pdoi_batch_id);
8238:
8239: l_err_loc := 900;
8240: ICX_CAT_UTIL_PVT.g_job_complete_date := SYSDATE;
8241: ICX_CAT_R12_UPGRADE_PVT.updateR12UpgradeJob(ICX_CAT_UTIL_PVT.g_job_complete_status);
8242:
8243: l_err_loc := 1000;
8244: x_retcode := 0;
8245: x_errbuf := l_api_name ||' done';
8273: l_api_name CONSTANT VARCHAR2(30) := 'createR12UpgradeJob';
8274: l_err_loc PLS_INTEGER;
8275: BEGIN
8276: l_err_loc := 100;
8277: INSERT INTO icx_cat_r12_upgrade_jobs
8278: (job_type, job_number, status, run_date, audsid, pdoi_batch_id,
8279: last_update_login, last_updated_by, last_update_date,
8280: created_by, creation_date, internal_request_id, request_id,
8281: program_application_id, program_id, program_login_id)
8289: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id,
8290: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id,
8291: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id
8292: FROM DUAL
8293: WHERE NOT EXISTS (SELECT 'x' FROM icx_cat_r12_upgrade_jobs
8294: WHERE job_type = ICX_CAT_UTIL_PVT.g_job_type
8295: AND job_number = ICX_CAT_UTIL_PVT.g_job_number );
8296:
8297: l_err_loc := 200;
8316: l_row_count PLS_INTEGER;
8317: BEGIN
8318: l_err_loc := 100;
8319: IF ( p_job_status = ICX_CAT_UTIL_PVT.g_job_running_status) THEN
8320: UPDATE icx_cat_r12_upgrade_jobs
8321: SET status = p_job_status,
8322: audsid = g_audsid,
8323: pdoi_batch_id = g_PDOI_batch_id,
8324: last_update_login = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
8336: -- we need to update the pdoi_update_date and pdoi_complete_date
8337: -- because when the parent is re-started, it is re-started in a
8338: -- new session and these global varaibles are lost
8339: l_err_loc := 200;
8340: UPDATE icx_cat_r12_upgrade_jobs
8341: SET preupg_pdoi_update_date = ICX_CAT_UTIL_PVT.g_job_pdoi_update_date,
8342: preupg_pdoi_complete_date = ICX_CAT_UTIL_PVT.g_job_pdoi_complete_date
8343: WHERE job_type = ICX_CAT_UTIL_PVT.g_job_type
8344: AND job_number = ICX_CAT_UTIL_PVT.g_job_number;
8344: AND job_number = ICX_CAT_UTIL_PVT.g_job_number;
8345: ELSE
8346: -- i.e. completed / failed status
8347: l_err_loc := 300;
8348: UPDATE icx_cat_r12_upgrade_jobs
8349: SET status = p_job_status,
8350: audsid2 = p_audsid2,
8351: preupg_complete_date = ICX_CAT_UTIL_PVT.g_job_complete_date,
8352: preupg_pdoi_update_date = NVL(ICX_CAT_UTIL_PVT.g_job_pdoi_update_date, preupg_pdoi_update_date),
8365: l_err_loc := 500;
8366: IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
8367: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
8368: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
8369: '; No: of rows updated in icx_cat_r12_upgrade_jobs:' || l_row_count);
8370: END IF;
8371:
8372: l_err_loc := 600;
8373: COMMIT;
8377: G_PKG_NAME, l_api_name,
8378: ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
8379: END updateR12UpgradeJob;
8380:
8381: END ICX_CAT_R12_UPGRADE_PVT;