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 3098: DELETE FROM icx_cat_items_ctx_dtls_tlp

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

Line 3125: INSERT INTO icx_cat_items_ctx_dtls_tlp

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

Line 3144: INSERT INTO icx_cat_items_ctx_dtls_tlp

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

Line 3163: INSERT INTO icx_cat_items_ctx_dtls_tlp

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

Line 3191: INSERT INTO icx_cat_items_ctx_dtls_tlp

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

Line 3220: INSERT INTO icx_cat_items_ctx_dtls_tlp

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

Line 3388: INSERT INTO icx_cat_items_ctx_dtls_tlp

3384: l_end_sequence := 5000;
3385: -- rows with sequence between 5001 and 9999 (local attributes) will be done after the
3386: -- final call to ICX_CAT_POPULATE_CTXSTRING_PVT.populateCtxCatgAtt in
3387: FORALL i in 1..gIDInventoryItemIdTbl.COUNT
3388: INSERT INTO icx_cat_items_ctx_dtls_tlp
3389: (inventory_item_id, po_line_id, req_template_name,
3390: req_template_line_num, org_id, language,
3391: last_update_login, last_updated_by, last_update_date,
3392: created_by, creation_date, internal_request_id, request_id,

Line 3406: FROM icx_cat_items_ctx_dtls_tlp

3402: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id,
3403: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id,
3404: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id,
3405: sequence, ctx_desc
3406: FROM icx_cat_items_ctx_dtls_tlp
3407: WHERE inventory_item_id = gIDInventoryItemIdTbl(i)
3408: AND po_line_id = gIDPoLineIdTbl(i)
3409: AND req_template_name = gIDReqTemplateNameTbl(i)
3410: AND req_template_line_num = gIDReqTemplateLineNumTbl(i)

Line 3427: INSERT INTO icx_cat_items_ctx_dtls_tlp

3423:
3424: l_action_mode := 'DELETE_SPECIFIC_CTX_DTLS';
3425: l_start_sequence := 101;
3426: FORALL i in 1..gDBLDInventoryItemIdTbl.COUNT
3427: INSERT INTO icx_cat_items_ctx_dtls_tlp
3428: (inventory_item_id, po_line_id, req_template_name,
3429: req_template_line_num, org_id, language,
3430: last_update_login, last_updated_by, last_update_date,
3431: created_by, creation_date, internal_request_id, request_id,

Line 3445: FROM icx_cat_items_ctx_dtls_tlp

3441: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id,
3442: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id,
3443: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id,
3444: sequence, ctx_desc
3445: FROM icx_cat_items_ctx_dtls_tlp
3446: WHERE inventory_item_id = gDBLDInventoryItemIdTbl(i)
3447: AND po_line_id = gDBLDPoLineIdTbl(i)
3448: AND req_template_name = gDBLDReqTemplateNameTbl(i)
3449: AND req_template_line_num = gDBLDReqTemplateLineNumTbl(i)

Line 3466: INSERT INTO icx_cat_items_ctx_dtls_tlp

3462:
3463: l_action_mode := 'DELETE_SUPPLIER_ROW_CTX_DTLS';
3464: -- l_sequence := 2;
3465: FORALL i in 1..gDSDInventoryItemIdTbl.COUNT
3466: INSERT INTO icx_cat_items_ctx_dtls_tlp
3467: (inventory_item_id, po_line_id, req_template_name,
3468: req_template_line_num, org_id, language,
3469: last_update_login, last_updated_by, last_update_date,
3470: created_by, creation_date, internal_request_id, request_id,

Line 3484: FROM icx_cat_items_ctx_dtls_tlp

3480: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id,
3481: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id,
3482: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id,
3483: sequence, ctx_desc
3484: FROM icx_cat_items_ctx_dtls_tlp
3485: WHERE inventory_item_id = gDSDInventoryItemIdTbl(i)
3486: AND po_line_id = gDSDPoLineIdTbl(i)
3487: AND req_template_name = gDSDReqTemplateNameTbl(i)
3488: AND req_template_line_num = gDSDReqTemplateLineNumTbl(i)

Line 3505: INSERT INTO icx_cat_items_ctx_dtls_tlp

3501:
3502: l_action_mode := 'DELETE_ITEMREV_ROW_CTX_DTLS';
3503: -- l_sequence := 5;
3504: FORALL i in 1..gDIRDInventoryItemIdTbl.COUNT
3505: INSERT INTO icx_cat_items_ctx_dtls_tlp
3506: (inventory_item_id, po_line_id, req_template_name,
3507: req_template_line_num, org_id, language,
3508: last_update_login, last_updated_by, last_update_date,
3509: created_by, creation_date, internal_request_id, request_id,

Line 3523: FROM icx_cat_items_ctx_dtls_tlp

3519: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id,
3520: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id,
3521: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id,
3522: sequence, ctx_desc
3523: FROM icx_cat_items_ctx_dtls_tlp
3524: WHERE inventory_item_id = gDIRDInventoryItemIdTbl(i)
3525: AND po_line_id = gDIRDPoLineIdTbl(i)
3526: AND req_template_name = gDIRDReqTemplateNameTbl(i)
3527: AND req_template_line_num = gDIRDReqTemplateLineNumTbl(i)

Line 3544: INSERT INTO icx_cat_items_ctx_dtls_tlp

3540:
3541: l_action_mode := 'DELETE_SHOPCATG_ROW_CTX_DTLS';
3542: -- l_sequence := 6;
3543: FORALL i in 1..gDSCDInventoryItemIdTbl.COUNT
3544: INSERT INTO icx_cat_items_ctx_dtls_tlp
3545: (inventory_item_id, po_line_id, req_template_name,
3546: req_template_line_num, org_id, language,
3547: last_update_login, last_updated_by, last_update_date,
3548: created_by, creation_date, internal_request_id, request_id,

Line 3562: FROM icx_cat_items_ctx_dtls_tlp

3558: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id,
3559: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id,
3560: ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id,
3561: sequence, ctx_desc
3562: FROM icx_cat_items_ctx_dtls_tlp
3563: WHERE inventory_item_id = gDSCDInventoryItemIdTbl(i)
3564: AND po_line_id = gDSCDPoLineIdTbl(i)
3565: AND req_template_name = gDSCDReqTemplateNameTbl(i)
3566: AND req_template_line_num = gDSCDReqTemplateLineNumTbl(i)

Line 3734: UPDATE icx_cat_items_ctx_dtls_tlp dtls

3730: END IF;
3731:
3732: l_err_loc := 1000;
3733: FORALL i IN 1..l_rowid_tbl.COUNT
3734: UPDATE icx_cat_items_ctx_dtls_tlp dtls
3735: SET ctx_desc = '<' || l_section_tag || '>' || replace(replace(p_vendor_name, '<', ' '), '>', ' ') || '',
3736: last_update_login = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
3737: last_updated_by = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
3738: last_update_date = sysdate,

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

3859:
3860: /*
3861: Steps:
3862: 1. update supplier_id, supplier_site_id and ctx_desc in icx_cat_items_ctx_hdrs_tlp
3863: 2. delete and insert into icx_cat_items_ctx_dtls_tlp : sequence 1 for supid and siteid
3864: 3. Check if supplier is searchable, if yes then update icx_cat_items_ctx_dtls_tlp : sequence 2 for supplier
3865: 4. call rebuild_index.
3866: */
3867: PROCEDURE populateVendorMerge

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

3860: /*
3861: Steps:
3862: 1. update supplier_id, supplier_site_id and ctx_desc in icx_cat_items_ctx_hdrs_tlp
3863: 2. delete and insert into icx_cat_items_ctx_dtls_tlp : sequence 1 for supid and siteid
3864: 3. Check if supplier is searchable, if yes then update icx_cat_items_ctx_dtls_tlp : sequence 2 for supplier
3865: 4. call rebuild_index.
3866: */
3867: PROCEDURE populateVendorMerge
3868: ( p_from_vendor_id IN NUMBER ,

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

3914: END IF;
3915:
3916: l_err_loc := 600;
3917: -- open the cursor for the dynamic sql to insert the
3918: -- row with sequence 1 in icx_cat_items_ctx_dtls_tlp only once
3919: l_csr_handle := DBMS_SQL.OPEN_CURSOR;
3920:
3921: l_err_loc := 700;
3922: WHILE l_continue LOOP

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

3958: l_err_loc := 1200;
3959:
3960: IF (l_rowid_tbl.COUNT > 0) THEN
3961: l_err_loc := 1300;
3962: -- delete the mandatory row in icx_cat_items_ctx_dtls_tlp i.e. the row with sequence = 1
3963: FORALL i IN 1..l_rowid_tbl.COUNT
3964: DELETE FROM icx_cat_items_ctx_dtls_tlp dtls
3965: WHERE sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqMandatoryBaseRow
3966: AND EXISTS ( SELECT 'x' FROM icx_cat_items_ctx_hdrs_tlp hdrs

Line 3964: DELETE FROM icx_cat_items_ctx_dtls_tlp dtls

3960: IF (l_rowid_tbl.COUNT > 0) THEN
3961: l_err_loc := 1300;
3962: -- delete the mandatory row in icx_cat_items_ctx_dtls_tlp i.e. the row with sequence = 1
3963: FORALL i IN 1..l_rowid_tbl.COUNT
3964: DELETE FROM icx_cat_items_ctx_dtls_tlp dtls
3965: WHERE sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqMandatoryBaseRow
3966: AND EXISTS ( SELECT 'x' FROM icx_cat_items_ctx_hdrs_tlp hdrs
3967: WHERE hdrs.po_line_id = dtls.po_line_id
3968: AND hdrs.req_template_name = dtls.req_template_name

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

4008: END IF;
4009:
4010: l_err_loc := 1900;
4011:
4012: -- insert the mandatory row in icx_cat_items_ctx_dtls_tlp i.e. the row with sequence = 1
4013: DBMS_SQL.BIND_VARIABLE(l_csr_handle,':B_sequence', l_ctx_sqlstring_rec.bind_sequence);
4014: DBMS_SQL.BIND_ARRAY(l_csr_handle, ':B_rowid', l_rowid_tbl);
4015: l_err_loc := 2000;
4016: l_status := DBMS_SQL.EXECUTE(l_csr_handle);

Line 4041: UPDATE icx_cat_items_ctx_dtls_tlp dtls

4037: l_err_loc := 2300;
4038: IF (l_searchable = 'Y') THEN
4039: l_err_loc := 2400;
4040: FORALL i IN 1..l_rowid_tbl.COUNT
4041: UPDATE icx_cat_items_ctx_dtls_tlp dtls
4042: SET ctx_desc = '<' || l_metadata_rec.section_tag || '>' ||
4043: replace(replace(l_to_vendor_name, '<', ' '), '>', ' ') ||
4044: '',
4045: last_update_login = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,

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

4089:
4090: l_err_loc := 2800;
4091:
4092: -- close the cursor for the dynamic sql to insert the
4093: -- row with sequence 1 in icx_cat_items_ctx_dtls_tlp only once
4094: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4095: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
4096: ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
4097: 'the dynamic sql is closed');

Line 4277: DELETE FROM icx_cat_items_ctx_dtls_tlp

4273: END IF;
4274:
4275: l_err_loc := 1000;
4276: FORALL j IN 1..l_po_line_id_tbl.COUNT
4277: DELETE FROM icx_cat_items_ctx_dtls_tlp
4278: WHERE po_line_id = l_po_line_id_tbl(j)
4279: AND req_template_name = l_req_template_name_tbl(j)
4280: AND req_template_line_num = l_req_template_line_num_tbl(j)
4281: AND inventory_item_id = l_inventory_item_id_tbl(j)

Line 4459: DELETE FROM icx_cat_items_ctx_dtls_tlp

4455: END IF;
4456:
4457: l_err_loc := 500;
4458: FORALL j IN 1..l_po_line_id_tbl.COUNT
4459: DELETE FROM icx_cat_items_ctx_dtls_tlp
4460: WHERE po_line_id = l_po_line_id_tbl(j)
4461: AND req_template_name = l_req_template_name_tbl(j)
4462: AND req_template_line_num = l_req_template_line_num_tbl(j)
4463: AND inventory_item_id = l_inventory_item_id_tbl(j)

Line 4580: DELETE FROM icx_cat_items_ctx_dtls_tlp

4576: END IF;
4577:
4578: l_err_loc := 500;
4579: FORALL j IN 1..l_po_line_id_tbl.COUNT
4580: DELETE FROM icx_cat_items_ctx_dtls_tlp
4581: WHERE po_line_id = l_po_line_id_tbl(j)
4582: AND req_template_name = l_req_template_name_tbl(j)
4583: AND req_template_line_num = l_req_template_line_num_tbl(j)
4584: AND inventory_item_id = l_inventory_item_id_tbl(j)

Line 4715: DELETE FROM icx_cat_items_ctx_dtls_tlp

4711: END IF;
4712:
4713: l_err_loc := 500;
4714: FORALL j IN 1..l_po_line_id_tbl.COUNT
4715: DELETE FROM icx_cat_items_ctx_dtls_tlp
4716: WHERE po_line_id = l_po_line_id_tbl(j)
4717: AND req_template_name = l_req_template_name_tbl(j)
4718: AND req_template_line_num = l_req_template_line_num_tbl(j)
4719: AND inventory_item_id = l_inventory_item_id_tbl(j)