eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.ICX_CAT_POPULATE_ITEM_PVT dependencies on ICX_CAT_ITEMS_CTX_DTLS_TLP

Line 60: -- INSERT icx_cat_items_ctx_dtls_tlp

56: gIHSupplierTbl DBMS_SQL.VARCHAR2_TABLE;
57: gIHGlobalAgreementFlagTbl DBMS_SQL.VARCHAR2_TABLE;
58: gIHMergedSourceTypeTbl DBMS_SQL.VARCHAR2_TABLE;
59:
60: -- INSERT icx_cat_items_ctx_dtls_tlp
61: gIDInventoryItemIdTbl DBMS_SQL.NUMBER_TABLE;
62: gIDPoLineIdTbl DBMS_SQL.NUMBER_TABLE;
63: gIDReqTemplateNameTbl DBMS_SQL.VARCHAR2_TABLE;
64: gIDReqTemplateLineNumTbl DBMS_SQL.NUMBER_TABLE;

Line 126: -- DELETE icx_cat_items_ctx_dtls_tlp

122: gDHReqTemplateLineNumTbl DBMS_SQL.NUMBER_TABLE;
123: gDHOrgIdTbl DBMS_SQL.NUMBER_TABLE;
124: gDHLanguageTbl DBMS_SQL.VARCHAR2_TABLE;
125:
126: -- DELETE icx_cat_items_ctx_dtls_tlp
127: gDDInventoryItemIdTbl DBMS_SQL.NUMBER_TABLE;
128: gDDPoLineIdTbl DBMS_SQL.NUMBER_TABLE;
129: gDDReqTemplateNameTbl DBMS_SQL.VARCHAR2_TABLE;
130: gDDReqTemplateLineNumTbl DBMS_SQL.NUMBER_TABLE;

Line 1437: -- Re-populate the row with sequence = 1 i.e. Mandatory row in icx_cat_items_ctx_dtls_tlp

1433: ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1434: RAISE;
1435: END deleteItemCtxDtlsTLP;
1436:
1437: -- Re-populate the row with sequence = 1 i.e. Mandatory row in icx_cat_items_ctx_dtls_tlp
1438: -- This row contains concatenated string of language, source_type, supid, ipcatid, pocatid and supsiteid
1439: PROCEDURE delMandatoryRowFromCtxDtlsTLP
1440: ( p_current_ctx_item_rec IN g_ctx_item_rec_type
1441: )

Line 1469: -- Re-populate the row with sequence = 2 i.e. Supplier row in icx_cat_items_ctx_dtls_tlp

1465: ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1466: RAISE;
1467: END delMandatoryRowFromCtxDtlsTLP;
1468:
1469: -- Re-populate the row with sequence = 2 i.e. Supplier row in icx_cat_items_ctx_dtls_tlp
1470: PROCEDURE delSupplierRowFromCtxDtlsTLP
1471: ( p_current_ctx_item_rec IN g_ctx_item_rec_type
1472: )
1473: IS

Line 1500: -- Re-populate the row with sequence = 5 i.e. Item Revision row in icx_cat_items_ctx_dtls_tlp

1496: ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1497: RAISE;
1498: END delSupplierRowFromCtxDtlsTLP;
1499:
1500: -- Re-populate the row with sequence = 5 i.e. Item Revision row in icx_cat_items_ctx_dtls_tlp
1501: PROCEDURE delItemRevRowFromCtxDtlsTLP
1502: ( p_current_ctx_item_rec IN g_ctx_item_rec_type
1503: )
1504: IS

Line 1531: -- Re-populate the row with sequence = 6 i.e. shopping_category row in icx_cat_items_ctx_dtls_tlp

1527: ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1528: RAISE;
1529: END delItemRevRowFromCtxDtlsTLP;
1530:
1531: -- Re-populate the row with sequence = 6 i.e. shopping_category row in icx_cat_items_ctx_dtls_tlp
1532: PROCEDURE delShopCatgRowFromCtxDtlsTLP
1533: ( p_current_ctx_item_rec IN g_ctx_item_rec_type
1534: )
1535: IS

Line 1562: -- Re-populate the row with sequence = 15001 i.e. purchorgid in icx_cat_items_ctx_dtls_tlp

1558: ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1559: RAISE;
1560: END delShopCatgRowFromCtxDtlsTLP;
1561:
1562: -- Re-populate the row with sequence = 15001 i.e. purchorgid in icx_cat_items_ctx_dtls_tlp
1563: PROCEDURE delPurchOrgRowFromCtxDtlsTLP
1564: ( p_current_ctx_item_rec IN g_ctx_item_rec_type
1565: )
1566: IS

Line 1716: -- purchasing_org_id row i.e. row with sequence = 15001 in icx_cat_items_ctx_dtls_tlp

1712:
1713: -- Removed the call to delPurchOrgRowFromCtxDtlsTLP,
1714: -- because it adds one delete script for upgrade case which is not necessary
1715: -- so add one more insert script for gIDInventoryItemIdTbl to insert the
1716: -- purchasing_org_id row i.e. row with sequence = 15001 in icx_cat_items_ctx_dtls_tlp
1717: -- delPurchOrgRowFromCtxDtlsTLP(p_current_ctx_item_rec);
1718:
1719: l_err_loc := 300;
1720: EXCEPTION

Line 1873: -- In icx_cat_items_ctx_dtls_tlp, need to

1869: l_err_loc := 1100;
1870: l_update_item_ctx_hdr_row := TRUE;
1871:
1872: -- re-create the intermedia ctxString in icx_cat_ctx_dtls_tlp
1873: -- In icx_cat_items_ctx_dtls_tlp, need to
1874: -- 1. remove some of the special rows depending on the changes and
1875: -- 2. remove the base and local attributes rows i.e. sequence > 100 and < 10000
1876:
1877: IF (p_current_ctx_item_rec.ctx_po_category_id <> p_current_ctx_item_rec.po_category_id OR

Line 2058: -- 1. Used to process the DML to icx_cat_items_ctx_dtls_tlp differently for GBPAs

2054: ICX_CAT_UTIL_PVT.g_QuoteCsr_const := 'Quote';
2055: ICX_CAT_UTIL_PVT.g_GBPACsr_const := 'GBPA';
2056: ICX_CAT_UTIL_PVT.g_ReqTemplateCsr_const := 'ReqTemplate';
2057: ICX_CAT_UTIL_PVT.g_MasterItemCsr_const := 'MASTER_ITEM';
2058: -- 1. Used to process the DML to icx_cat_items_ctx_dtls_tlp differently for GBPAs
2059: -- 2. Used to call the appropriate buildCtxSqls, list of ctx sqls are different depending upon the source
2060: -- req_templates and master items dont need to run the sql for contract_num
2061: -- master items need to only run sql with sequence 1 and org info.
2062: */

Line 2853: DELETE FROM icx_cat_items_ctx_dtls_tlp

2849:
2850: l_err_loc := 450;
2851: l_action_mode := 'DELETE_CTX_DTLS';
2852: FORALL i in 1..gDDInventoryItemIdTbl.COUNT
2853: DELETE FROM icx_cat_items_ctx_dtls_tlp
2854: WHERE inventory_item_id = gDDInventoryItemIdTbl(i)
2855: AND po_line_id = gDDPoLineIdTbl(i)
2856: AND req_template_name = gDDReqTemplateNameTbl(i)
2857: AND req_template_line_num = gDDReqTemplateLineNumTbl(i)

Line 2987: DELETE FROM icx_cat_items_ctx_dtls_tlp

2983: l_action_mode := 'DELETE_MANDATORY_ROW_CTX_DTLS';
2984: -- DELETE rows with sequence = 1 in icx_cat_items_ctx_dtl_tlp
2985: -- l_sequence := 1;
2986: FORALL i in 1..gDMDInventoryItemIdTbl.COUNT
2987: DELETE FROM icx_cat_items_ctx_dtls_tlp
2988: WHERE inventory_item_id = gDMDInventoryItemIdTbl(i)
2989: AND po_line_id = gDMDPoLineIdTbl(i)
2990: AND req_template_name = gDMDReqTemplateNameTbl(i)
2991: AND req_template_line_num = gDMDReqTemplateLineNumTbl(i)

Line 3009: DELETE FROM icx_cat_items_ctx_dtls_tlp

3005: l_action_mode := 'DELETE_SUPPLIER_ROW_CTX_DTLS';
3006: -- DELETE rows with sequence = 2 in icx_cat_items_ctx_dtl_tlp
3007: -- l_sequence := 2;
3008: FORALL i in 1..gDSDInventoryItemIdTbl.COUNT
3009: DELETE FROM icx_cat_items_ctx_dtls_tlp
3010: WHERE inventory_item_id = gDSDInventoryItemIdTbl(i)
3011: AND po_line_id = gDSDPoLineIdTbl(i)
3012: AND req_template_name = gDSDReqTemplateNameTbl(i)
3013: AND req_template_line_num = gDSDReqTemplateLineNumTbl(i)

Line 3031: DELETE FROM icx_cat_items_ctx_dtls_tlp

3027: l_action_mode := 'DELETE_ITEMREV_ROW_CTX_DTLS';
3028: -- DELETE rows with sequence = 5 in icx_cat_items_ctx_dtl_tlp
3029: -- l_sequence := 5;
3030: FORALL i in 1..gDIRDInventoryItemIdTbl.COUNT
3031: DELETE FROM icx_cat_items_ctx_dtls_tlp
3032: WHERE inventory_item_id = gDIRDInventoryItemIdTbl(i)
3033: AND po_line_id = gDIRDPoLineIdTbl(i)
3034: AND req_template_name = gDIRDReqTemplateNameTbl(i)
3035: AND req_template_line_num = gDIRDReqTemplateLineNumTbl(i)

Line 3053: DELETE FROM icx_cat_items_ctx_dtls_tlp

3049: l_action_mode := 'DELETE_SHOPCATG_ROW_CTX_DTLS';
3050: -- DELETE rows with sequence = 6 in icx_cat_items_ctx_dtl_tlp
3051: -- l_sequence := 6;
3052: FORALL i in 1..gDSCDInventoryItemIdTbl.COUNT
3053: DELETE FROM icx_cat_items_ctx_dtls_tlp
3054: WHERE inventory_item_id = gDSCDInventoryItemIdTbl(i)
3055: AND po_line_id = gDSCDPoLineIdTbl(i)
3056: AND req_template_name = gDSCDReqTemplateNameTbl(i)
3057: AND req_template_line_num = gDSCDReqTemplateLineNumTbl(i)

Line 3075: DELETE FROM icx_cat_items_ctx_dtls_tlp

3071: l_action_mode := 'DELETE_PURCHORG_ROW_CTX_DTLS';
3072: -- DELETE rows with sequence = 15001 in icx_cat_items_ctx_dtl_tlp
3073: -- l_sequence := 15001;
3074: FORALL i in 1..gDPODInventoryItemIdTbl.COUNT
3075: DELETE FROM icx_cat_items_ctx_dtls_tlp
3076: WHERE inventory_item_id = gDPODInventoryItemIdTbl(i)
3077: AND po_line_id = gDPODPoLineIdTbl(i)
3078: AND req_template_name = gDPODReqTemplateNameTbl(i)
3079: AND req_template_line_num = gDPODReqTemplateLineNumTbl(i)

Line 3099: DELETE FROM icx_cat_items_ctx_dtls_tlp

3095: -- DELETE rows with sequence > 100 and < 10000 icx_cat_items_ctx_dtl_tlp
3096: l_start_sequence := 101;
3097: l_end_sequence := 9999;
3098: FORALL i in 1..gDBLDInventoryItemIdTbl.COUNT
3099: DELETE FROM icx_cat_items_ctx_dtls_tlp
3100: WHERE inventory_item_id = gDBLDInventoryItemIdTbl(i)
3101: AND po_line_id = gDBLDPoLineIdTbl(i)
3102: AND req_template_name = gDBLDReqTemplateNameTbl(i)
3103: AND req_template_line_num = gDBLDReqTemplateLineNumTbl(i)

Line 3126: INSERT INTO icx_cat_items_ctx_dtls_tlp

3122: -- 15000
3123: -- 15001 || to_char(gIDPurchasingOrgIdTbl(i)) ||
3124: -- 15001 || to_char(gDPODPurchasingOrgIdTbl(i)) ||
3125: FORALL i in 1..gIDInventoryItemIdTbl.COUNT
3126: INSERT INTO icx_cat_items_ctx_dtls_tlp
3127: (inventory_item_id, po_line_id, req_template_name, req_template_line_num,
3128: org_id, language, sequence, ctx_desc,
3129: last_update_login, last_updated_by, last_update_date,
3130: created_by, creation_date, internal_request_id, request_id,

Line 3145: INSERT INTO icx_cat_items_ctx_dtls_tlp

3141: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id);
3142:
3143: l_err_loc := 1400;
3144: FORALL i in 1..gIDInventoryItemIdTbl.COUNT
3145: INSERT INTO icx_cat_items_ctx_dtls_tlp
3146: (inventory_item_id, po_line_id, req_template_name, req_template_line_num,
3147: org_id, language, sequence, ctx_desc,
3148: last_update_login, last_updated_by, last_update_date,
3149: created_by, creation_date, internal_request_id, request_id,

Line 3164: INSERT INTO icx_cat_items_ctx_dtls_tlp

3160: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id);
3161:
3162: l_err_loc := 1450;
3163: FORALL i in 1..gIDInventoryItemIdTbl.COUNT
3164: INSERT INTO icx_cat_items_ctx_dtls_tlp
3165: (inventory_item_id, po_line_id, req_template_name, req_template_line_num,
3166: org_id, language, sequence, ctx_desc,
3167: last_update_login, last_updated_by, last_update_date,
3168: created_by, creation_date, internal_request_id, request_id,

Line 3192: INSERT INTO icx_cat_items_ctx_dtls_tlp

3188:
3189: l_err_loc := 1460;
3190: -- Purchasing_org_id row needs to be populated for new rows i.e. gIDInventoryItemIdTbl and gDPODInventoryItemIdTbl
3191: FORALL i in 1..gIDInventoryItemIdTbl.COUNT
3192: INSERT INTO icx_cat_items_ctx_dtls_tlp
3193: (inventory_item_id, po_line_id, req_template_name, req_template_line_num,
3194: org_id, language, sequence, ctx_desc,
3195: last_update_login, last_updated_by, last_update_date,
3196: created_by, creation_date, internal_request_id, request_id,

Line 3221: INSERT INTO icx_cat_items_ctx_dtls_tlp

3217: l_err_loc := 1500;
3218: l_action_mode := 'DELETE_PURCHORG_ROW_CTX_DTLS';
3219: -- Purchasing_org_id row needs to be populated for new rows i.e. gIDInventoryItemIdTbl and gDPODInventoryItemIdTbl
3220: FORALL i in 1..gDPODInventoryItemIdTbl.COUNT
3221: INSERT INTO icx_cat_items_ctx_dtls_tlp
3222: (inventory_item_id, po_line_id, req_template_name, req_template_line_num,
3223: org_id, language, sequence, ctx_desc,
3224: last_update_login, last_updated_by, last_update_date,
3225: created_by, creation_date, internal_request_id, request_id,

Line 3412: INSERT INTO icx_cat_items_ctx_dtls_tlp

3408: l_end_sequence := 5000;
3409: -- rows with sequence between 5001 and 9999 (local attributes) will be done after the
3410: -- final call to ICX_CAT_POPULATE_CTXSTRING_PVT.populateCtxCatgAtt in
3411: FORALL i in 1..gIDInventoryItemIdTbl.COUNT
3412: INSERT INTO icx_cat_items_ctx_dtls_tlp
3413: (inventory_item_id, po_line_id, req_template_name,
3414: req_template_line_num, org_id, language,
3415: last_update_login, last_updated_by, last_update_date,
3416: created_by, creation_date, internal_request_id, request_id,

Line 3430: FROM icx_cat_items_ctx_dtls_tlp

3426: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id,
3427: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id,
3428: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id,
3429: sequence, ctx_desc
3430: FROM icx_cat_items_ctx_dtls_tlp
3431: WHERE inventory_item_id = gIDInventoryItemIdTbl(i)
3432: AND po_line_id = gIDPoLineIdTbl(i)
3433: AND req_template_name = gIDReqTemplateNameTbl(i)
3434: AND req_template_line_num = gIDReqTemplateLineNumTbl(i)

Line 3451: INSERT INTO icx_cat_items_ctx_dtls_tlp

3447:
3448: l_action_mode := 'DELETE_SPECIFIC_CTX_DTLS';
3449: l_start_sequence := 101;
3450: FORALL i in 1..gDBLDInventoryItemIdTbl.COUNT
3451: INSERT INTO icx_cat_items_ctx_dtls_tlp
3452: (inventory_item_id, po_line_id, req_template_name,
3453: req_template_line_num, org_id, language,
3454: last_update_login, last_updated_by, last_update_date,
3455: created_by, creation_date, internal_request_id, request_id,

Line 3469: FROM icx_cat_items_ctx_dtls_tlp

3465: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id,
3466: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id,
3467: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id,
3468: sequence, ctx_desc
3469: FROM icx_cat_items_ctx_dtls_tlp
3470: WHERE inventory_item_id = gDBLDInventoryItemIdTbl(i)
3471: AND po_line_id = gDBLDPoLineIdTbl(i)
3472: AND req_template_name = gDBLDReqTemplateNameTbl(i)
3473: AND req_template_line_num = gDBLDReqTemplateLineNumTbl(i)

Line 3491: INSERT INTO icx_cat_items_ctx_dtls_tlp

3487:
3488: l_action_mode := 'DELETE_SUPPLIER_ROW_CTX_DTLS';
3489: -- l_sequence := 2;
3490: FORALL i in 1..gDSDInventoryItemIdTbl.COUNT
3491: INSERT INTO icx_cat_items_ctx_dtls_tlp
3492: (inventory_item_id, po_line_id, req_template_name,
3493: req_template_line_num, org_id, language,
3494: last_update_login, last_updated_by, last_update_date,
3495: created_by, creation_date, internal_request_id, request_id,

Line 3509: FROM icx_cat_items_ctx_dtls_tlp

3505: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id,
3506: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id,
3507: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id,
3508: sequence, ctx_desc
3509: FROM icx_cat_items_ctx_dtls_tlp
3510: WHERE inventory_item_id = gDSDInventoryItemIdTbl(i)
3511: AND po_line_id = gDSDPoLineIdTbl(i)
3512: AND req_template_name = gDSDReqTemplateNameTbl(i)
3513: AND req_template_line_num = gDSDReqTemplateLineNumTbl(i)

Line 3530: INSERT INTO icx_cat_items_ctx_dtls_tlp

3526:
3527: l_action_mode := 'DELETE_ITEMREV_ROW_CTX_DTLS';
3528: -- l_sequence := 5;
3529: FORALL i in 1..gDIRDInventoryItemIdTbl.COUNT
3530: INSERT INTO icx_cat_items_ctx_dtls_tlp
3531: (inventory_item_id, po_line_id, req_template_name,
3532: req_template_line_num, org_id, language,
3533: last_update_login, last_updated_by, last_update_date,
3534: created_by, creation_date, internal_request_id, request_id,

Line 3548: FROM icx_cat_items_ctx_dtls_tlp

3544: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id,
3545: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id,
3546: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id,
3547: sequence, ctx_desc
3548: FROM icx_cat_items_ctx_dtls_tlp
3549: WHERE inventory_item_id = gDIRDInventoryItemIdTbl(i)
3550: AND po_line_id = gDIRDPoLineIdTbl(i)
3551: AND req_template_name = gDIRDReqTemplateNameTbl(i)
3552: AND req_template_line_num = gDIRDReqTemplateLineNumTbl(i)

Line 3569: INSERT INTO icx_cat_items_ctx_dtls_tlp

3565:
3566: l_action_mode := 'DELETE_SHOPCATG_ROW_CTX_DTLS';
3567: -- l_sequence := 6;
3568: FORALL i in 1..gDSCDInventoryItemIdTbl.COUNT
3569: INSERT INTO icx_cat_items_ctx_dtls_tlp
3570: (inventory_item_id, po_line_id, req_template_name,
3571: req_template_line_num, org_id, language,
3572: last_update_login, last_updated_by, last_update_date,
3573: created_by, creation_date, internal_request_id, request_id,

Line 3587: FROM icx_cat_items_ctx_dtls_tlp

3583: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id,
3584: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id,
3585: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id,
3586: sequence, ctx_desc
3587: FROM icx_cat_items_ctx_dtls_tlp
3588: WHERE inventory_item_id = gDSCDInventoryItemIdTbl(i)
3589: AND po_line_id = gDSCDPoLineIdTbl(i)
3590: AND req_template_name = gDSCDReqTemplateNameTbl(i)
3591: AND req_template_line_num = gDSCDReqTemplateLineNumTbl(i)

Line 3759: UPDATE icx_cat_items_ctx_dtls_tlp dtls

3755: END IF;
3756:
3757: l_err_loc := 1000;
3758: FORALL i IN 1..l_rowid_tbl.COUNT
3759: UPDATE icx_cat_items_ctx_dtls_tlp dtls
3760: SET ctx_desc = '<' || l_section_tag || '>' || replace(replace(p_vendor_name, '<', ' '), '>', ' ') || '',
3761: last_update_login = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
3762: last_updated_by = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
3763: last_update_date = sysdate,

Line 3888: 2. delete and insert into icx_cat_items_ctx_dtls_tlp : sequence 1 for supid and siteid

3884:
3885: /*
3886: Steps:
3887: 1. update supplier_id, supplier_site_id and ctx_desc in icx_cat_items_ctx_hdrs_tlp
3888: 2. delete and insert into icx_cat_items_ctx_dtls_tlp : sequence 1 for supid and siteid
3889: 3. Check if supplier is searchable, if yes then update icx_cat_items_ctx_dtls_tlp : sequence 2 for supplier
3890: 4. call rebuild_index.
3891: */
3892: PROCEDURE populateVendorMerge

Line 3889: 3. Check if supplier is searchable, if yes then update icx_cat_items_ctx_dtls_tlp : sequence 2 for supplier

3885: /*
3886: Steps:
3887: 1. update supplier_id, supplier_site_id and ctx_desc in icx_cat_items_ctx_hdrs_tlp
3888: 2. delete and insert into icx_cat_items_ctx_dtls_tlp : sequence 1 for supid and siteid
3889: 3. Check if supplier is searchable, if yes then update icx_cat_items_ctx_dtls_tlp : sequence 2 for supplier
3890: 4. call rebuild_index.
3891: */
3892: PROCEDURE populateVendorMerge
3893: ( p_from_vendor_id IN NUMBER ,

Line 3943: -- row with sequence 1 in icx_cat_items_ctx_dtls_tlp only once

3939: END IF;
3940:
3941: l_err_loc := 600;
3942: -- open the cursor for the dynamic sql to insert the
3943: -- row with sequence 1 in icx_cat_items_ctx_dtls_tlp only once
3944: l_csr_handle := DBMS_SQL.OPEN_CURSOR;
3945:
3946: l_err_loc := 700;
3947: WHILE l_continue LOOP

Line 3987: -- delete the mandatory row in icx_cat_items_ctx_dtls_tlp i.e. the row with sequence = 1

3983: l_err_loc := 1200;
3984:
3985: IF (l_rowid_tbl.COUNT > 0) THEN
3986: l_err_loc := 1300;
3987: -- delete the mandatory row in icx_cat_items_ctx_dtls_tlp i.e. the row with sequence = 1
3988: FORALL i IN 1..l_rowid_tbl.COUNT
3989: DELETE FROM icx_cat_items_ctx_dtls_tlp dtls
3990: WHERE sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqMandatoryBaseRow
3991: AND EXISTS ( SELECT 'x' FROM icx_cat_items_ctx_hdrs_tlp hdrs

Line 3989: DELETE FROM icx_cat_items_ctx_dtls_tlp dtls

3985: IF (l_rowid_tbl.COUNT > 0) THEN
3986: l_err_loc := 1300;
3987: -- delete the mandatory row in icx_cat_items_ctx_dtls_tlp i.e. the row with sequence = 1
3988: FORALL i IN 1..l_rowid_tbl.COUNT
3989: DELETE FROM icx_cat_items_ctx_dtls_tlp dtls
3990: WHERE sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqMandatoryBaseRow
3991: AND EXISTS ( SELECT 'x' FROM icx_cat_items_ctx_hdrs_tlp hdrs
3992: WHERE hdrs.po_line_id = dtls.po_line_id
3993: AND hdrs.req_template_name = dtls.req_template_name

Line 4037: -- insert the mandatory row in icx_cat_items_ctx_dtls_tlp i.e. the row with sequence = 1

4033: END IF;
4034:
4035: l_err_loc := 1900;
4036:
4037: -- insert the mandatory row in icx_cat_items_ctx_dtls_tlp i.e. the row with sequence = 1
4038: DBMS_SQL.BIND_VARIABLE(l_csr_handle,':B_sequence', l_ctx_sqlstring_rec.bind_sequence);
4039: DBMS_SQL.BIND_ARRAY(l_csr_handle, ':B_rowid', l_rowid_tbl);
4040: l_err_loc := 2000;
4041: l_status := DBMS_SQL.EXECUTE(l_csr_handle);

Line 4066: UPDATE icx_cat_items_ctx_dtls_tlp dtls

4062: l_err_loc := 2300;
4063: IF (l_searchable = 'Y') THEN
4064: l_err_loc := 2400;
4065: FORALL i IN 1..l_rowid_tbl.COUNT
4066: UPDATE icx_cat_items_ctx_dtls_tlp dtls
4067: SET ctx_desc = '<' || l_metadata_rec.section_tag || '>' ||
4068: replace(replace(l_to_vendor_name, '<', ' '), '>', ' ') ||
4069: '',
4070: last_update_login = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,

Line 4118: -- row with sequence 1 in icx_cat_items_ctx_dtls_tlp only once

4114:
4115: l_err_loc := 2800;
4116:
4117: -- close the cursor for the dynamic sql to insert the
4118: -- row with sequence 1 in icx_cat_items_ctx_dtls_tlp only once
4119: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4120: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
4121: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
4122: 'the dynamic sql is closed');

Line 4302: DELETE FROM icx_cat_items_ctx_dtls_tlp

4298: END IF;
4299:
4300: l_err_loc := 1000;
4301: FORALL j IN 1..l_po_line_id_tbl.COUNT
4302: DELETE FROM icx_cat_items_ctx_dtls_tlp
4303: WHERE po_line_id = l_po_line_id_tbl(j)
4304: AND req_template_name = l_req_template_name_tbl(j)
4305: AND req_template_line_num = l_req_template_line_num_tbl(j)
4306: AND inventory_item_id = l_inventory_item_id_tbl(j)

Line 4484: DELETE FROM icx_cat_items_ctx_dtls_tlp

4480: END IF;
4481:
4482: l_err_loc := 500;
4483: FORALL j IN 1..l_po_line_id_tbl.COUNT
4484: DELETE FROM icx_cat_items_ctx_dtls_tlp
4485: WHERE po_line_id = l_po_line_id_tbl(j)
4486: AND req_template_name = l_req_template_name_tbl(j)
4487: AND req_template_line_num = l_req_template_line_num_tbl(j)
4488: AND inventory_item_id = l_inventory_item_id_tbl(j)

Line 4605: DELETE FROM icx_cat_items_ctx_dtls_tlp

4601: END IF;
4602:
4603: l_err_loc := 500;
4604: FORALL j IN 1..l_po_line_id_tbl.COUNT
4605: DELETE FROM icx_cat_items_ctx_dtls_tlp
4606: WHERE po_line_id = l_po_line_id_tbl(j)
4607: AND req_template_name = l_req_template_name_tbl(j)
4608: AND req_template_line_num = l_req_template_line_num_tbl(j)
4609: AND inventory_item_id = l_inventory_item_id_tbl(j)

Line 4740: DELETE FROM icx_cat_items_ctx_dtls_tlp

4736: END IF;
4737:
4738: l_err_loc := 500;
4739: FORALL j IN 1..l_po_line_id_tbl.COUNT
4740: DELETE FROM icx_cat_items_ctx_dtls_tlp
4741: WHERE po_line_id = l_po_line_id_tbl(j)
4742: AND req_template_name = l_req_template_name_tbl(j)
4743: AND req_template_line_num = l_req_template_line_num_tbl(j)
4744: AND inventory_item_id = l_inventory_item_id_tbl(j)