[Home] [Help]
1: PACKAGE BODY PO_ATTRIBUTE_VALUES_PVT AS
2: /* $Header: PO_ATTRIBUTE_VALUES_PVT.plb 120.44.12020000.3 2013/02/10 16:49:47 vegajula ship $ */
3:
4: -- The module base for this package.
5: D_PACKAGE_BASE CONSTANT VARCHAR2(50) := PO_LOG.get_package_base('PO_ATTRIBUTE_VALUES_PVT');
1: PACKAGE BODY PO_ATTRIBUTE_VALUES_PVT AS
2: /* $Header: PO_ATTRIBUTE_VALUES_PVT.plb 120.44.12020000.3 2013/02/10 16:49:47 vegajula ship $ */
3:
4: -- The module base for this package.
5: D_PACKAGE_BASE CONSTANT VARCHAR2(50) := PO_LOG.get_package_base('PO_ATTRIBUTE_VALUES_PVT');
6:
1: PACKAGE BODY PO_ATTRIBUTE_VALUES_PVT AS
2: /* $Header: PO_ATTRIBUTE_VALUES_PVT.plb 120.44.12020000.3 2013/02/10 16:49:47 vegajula ship $ */
3:
4: -- The module base for this package.
5: D_PACKAGE_BASE CONSTANT VARCHAR2(50) := PO_LOG.get_package_base('PO_ATTRIBUTE_VALUES_PVT');
6:
7: -- The module base for the subprogram.
8: D_handle_attributes CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'handle_attributes');
9: D_set_attribute_values CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'set_attribute_values');
53: FROM ICX_CAT_AGREEMENT_ATTRS_V -- replaced ICX_CAT_ATTRIBUTES_TL
54: WHERE rt_category_id = p_category_id
55: AND language = p_language
56: AND type IN (0,1)
57: AND stored_in_table = 'PO_ATTRIBUTE_VALUES'
58: ORDER BY attribute_id, stored_in_table;
59:
60: -- cursor to select the translatable descriptors (type is 2) of
61: -- a particular category or base
75: FROM ICX_CAT_AGREEMENT_ATTRS_V -- replaced ICX_CAT_ATTRIBUTES_TL
76: WHERE rt_category_id = p_category_id
77: AND language = p_language
78: AND type = 2
79: AND stored_in_table = 'PO_ATTRIBUTE_VALUES_TLP'
80: ORDER BY attribute_id, stored_in_table;
81:
82: -- Cursor to select data from po_attribute_values
83: CURSOR attr_values_csr
78: AND type = 2
79: AND stored_in_table = 'PO_ATTRIBUTE_VALUES_TLP'
80: ORDER BY attribute_id, stored_in_table;
81:
82: -- Cursor to select data from po_attribute_values
83: CURSOR attr_values_csr
84: (
85: p_interface_header_id NUMBER,
86: p_po_header_id NUMBER
88: IS
89: SELECT PAII.interface_line_number,
90: PAV.*
91: FROM PO_LINES_ALL POL,
92: PO_ATTRIBUTE_VALUES PAV,
93: PON_AUC_ITEMS_INTERFACE PAII
94: WHERE POL.po_header_id = p_po_header_id
95: AND POL.po_line_id = pav.po_line_id
96: AND PAII.source_doc_id = p_po_header_id
96: AND PAII.source_doc_id = p_po_header_id
97: AND PAII.source_line_id = pol.po_line_id
98: AND PAII.interface_auction_header_id = p_interface_header_id;
99:
100: -- Cursor to select data from po_attribute_values_tlp
101: CURSOR attr_values_tlp_csr
102: (
103: p_interface_header_id NUMBER,
104: p_po_header_id NUMBER,
107: IS
108: SELECT PAII.interface_line_number,
109: PAVT.*
110: FROM PO_LINES_ALL POL,
111: PO_ATTRIBUTE_VALUES_TLP PAVT,
112: PON_AUC_ITEMS_INTERFACE PAII
113: WHERE POL.po_header_id = p_po_header_id
114: AND POL.po_line_id = pavt.po_line_id
115: AND PAVT.language = p_language
116: AND PAII.source_doc_id = p_po_header_id
117: AND PAII.source_line_id = pol.po_line_id
118: AND PAII.interface_auction_header_id = p_interface_header_id;
119:
120: -- Cursor to select data from po_attribute_values
121: -- This is to facilitate callback from Sourcing in the req to negotiation flow
122: CURSOR pon_attr_values_csr
123: (
124: p_auction_header_id NUMBER
133: AND PB.requisition_line_id = PRL.requisition_line_id
134: AND PRL.blanket_po_header_id IS NOT NULL
135: AND PRL.blanket_po_line_num IS NOT NULL
136: GROUP BY PB.auction_header_id,PB.line_number, blanket_po_header_id, blanket_po_line_num) PBR,
137: PO_ATTRIBUTE_VALUES PAV,
138: PO_LINES_ALL POL
139: WHERE PAIP.auction_header_id = PBR.auction_header_id
140: AND PAIP.line_number = PBR.line_number
141: AND POL.po_header_id = PBR.blanket_po_header_id
141: AND POL.po_header_id = PBR.blanket_po_header_id
142: AND POL.line_num = PBR.blanket_po_line_num
143: AND PAV.po_line_id = POL.po_line_id;
144:
145: -- Cursor to select data from po_attribute_values
146: -- This is to facilitate callback from Sourcing in the req to negotiation flow
147: CURSOR pon_attr_values_tlp_csr
148: (
149: p_auction_header_id NUMBER,
160: AND PB.requisition_line_id = PRL.requisition_line_id
161: AND PRL.blanket_po_header_id IS NOT NULL
162: AND PRL.blanket_po_line_num IS NOT NULL
163: GROUP BY PB.auction_header_id,PB.line_number, blanket_po_header_id, blanket_po_line_num) PBR,
164: PO_ATTRIBUTE_VALUES_TLP PAVT,
165: PO_LINES_ALL POL
166: WHERE PAIP.auction_header_id = PBR.auction_header_id
167: AND PAIP.line_number = PBR.line_number
168: AND POL.po_header_id = PBR.blanket_po_header_id
1620: PO_LOG.proc_begin(d_mod,'p_interface_header_id',p_interface_header_id);
1621: END IF;
1622:
1623: l_progress := '020';
1624: INSERT INTO PO_ATTRIBUTE_VALUES (
1625: ATTACHMENT_URL,
1626: ATTRIBUTE_VALUES_ID,
1627: AVAILABILITY,
1628: CREATED_BY,
1949: UNSPSC,
1950: LAST_UPDATED_PROGRAM)
1951: SELECT
1952: ATTACHMENT_URL,
1953: PO_ATTRIBUTE_VALUES_S.nextval,
1954: AVAILABILITY,
1955: CREATED_BY,
1956: CREATION_DATE,
1957: INVENTORY_ITEM_ID,
2277: 'AUTOCREATE_BACKEND_FOR_SOURCING'
2278: FROM PO_ATTR_VALUES_INTERFACE
2279: WHERE interface_header_id = p_interface_header_id;
2280:
2281: IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,l_progress,'Number of rows inserted in PO_ATTRIBUTE_VALUES table='||SQL%rowcount); END IF;
2282:
2283: l_progress := '030';
2284: --insert tlp records from interface table
2285: INSERT INTO PO_ATTRIBUTE_VALUES_TLP (
2281: IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,l_progress,'Number of rows inserted in PO_ATTRIBUTE_VALUES table='||SQL%rowcount); END IF;
2282:
2283: l_progress := '030';
2284: --insert tlp records from interface table
2285: INSERT INTO PO_ATTRIBUTE_VALUES_TLP (
2286: ALIAS,
2287: ATTRIBUTE_VALUES_TLP_ID,
2288: COMMENTS,
2289: CREATED_BY,
2457: TL_TEXT_CAT_ATTRIBUTE9,
2458: LAST_UPDATED_PROGRAM)
2459: SELECT
2460: ALIAS,
2461: PO_ATTRIBUTE_VALUES_TLP_S.nextval,
2462: COMMENTS,
2463: CREATED_BY,
2464: CREATION_DATE,
2465: DESCRIPTION,
2632: 'AUTOCREATE_BACKEND_FOR_SOURCING'
2633: FROM PO_ATTR_VALUES_TLP_INTERFACE
2634: WHERE interface_header_id = p_interface_header_id;
2635:
2636: IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,l_progress,'Number of rows inserted in PO_ATTRIBUTE_VALUES_TLP table='||SQL%rowcount); END IF;
2637:
2638: IF PO_LOG.d_proc THEN PO_LOG.proc_end(d_mod); END IF;
2639: EXCEPTION
2640: WHEN OTHERS THEN
2722: MTL.language,
2723: MTL.long_description -- Bug7039409: Get long_description also
2724: FROM MTL_SYSTEM_ITEMS_TL MTL,
2725: FINANCIALS_SYSTEM_PARAMS_ALL FSP,
2726: PO_ATTRIBUTE_VALUES_TLP TLP
2727: WHERE TLP.inventory_item_id IS NOT NULL
2728: AND TLP.inventory_item_id <> g_ATTR_VALUES_NULL_ID -- '-2'
2729: AND TLP.attribute_values_tlp_id = p_attr_values_tlp_id_list(i)
2730: AND MTL.inventory_item_id = TLP.inventory_item_id
2742: TLP.description,
2743: FNDLANG.language_code,
2744: NULL -- Bug7039409: long_description as NULL for one-time item
2745: FROM FND_LANGUAGES FNDLANG,
2746: PO_ATTRIBUTE_VALUES_TLP TLP
2747: WHERE (TLP.inventory_item_id IS NULL OR
2748: TLP.inventory_item_id = g_ATTR_VALUES_NULL_ID) -- '-2'
2749: AND FNDLANG.installed_flag IN ('B', 'I')
2750: AND FNDLANG.language_code <> TLP.language
2804: IS
2805: d_mod CONSTANT VARCHAR2(100) := D_get_tlp_ids_for_lines;
2806: l_progress VARCHAR2(4);
2807:
2808: l_attr_values_tlp_id PO_ATTRIBUTE_VALUES_TLP.attribute_values_tlp_id%TYPE;
2809: l_key PO_SESSION_GT.key%TYPE;
2810: BEGIN
2811: l_progress := '010';
2812:
2830: num1
2831: )
2832: SELECT l_key,
2833: TLP.attribute_values_tlp_id
2834: FROM PO_ATTRIBUTE_VALUES_TLP TLP,
2835: PO_LINES_ALL POL,
2836: PO_HEADERS_ALL POH
2837: WHERE TLP.po_line_id = p_po_line_id_list(i)
2838: AND p_po_line_id_list(i) <> g_NOT_REQUIRED_ID
2894: --------------------------------------------------------------------------------
2895: PROCEDURE create_translations
2896: (
2897: p_doc_type IN VARCHAR2, -- 'BLANKET', 'QUOTATION', 'REQ_TEMPLATE'
2898: p_default_lang_tlp_id IN PO_ATTRIBUTE_VALUES_TLP.attribute_values_tlp_id%TYPE DEFAULT NULL,
2899: p_po_line_id IN PO_LINES.po_line_id%TYPE DEFAULT NULL,
2900: p_default_lang_tlp_id_list IN PO_TBL_NUMBER DEFAULT NULL,
2901: p_po_line_id_list IN PO_TBL_NUMBER DEFAULT NULL,
2902: p_req_template_name IN PO_REQEXPRESS_LINES_ALL.express_name%TYPE DEFAULT NULL,
2906: IS
2907: d_mod CONSTANT VARCHAR2(100) := D_create_translations;
2908: l_progress VARCHAR2(4);
2909:
2910: l_attr_values_tlp_id PO_ATTRIBUTE_VALUES_TLP.attribute_values_tlp_id%TYPE;
2911: l_attr_values_tlp_id_list PO_TBL_NUMBER := PO_TBL_NUMBER();
2912: l_tlp_id_to_be_copied_list PO_TBL_NUMBER := PO_TBL_NUMBER();
2913: l_tlp_new_descriptions_list PO_TBL_VARCHAR480;-- := PO_TBL_VARCHAR480();
2914: l_tlp_language_list PO_TBL_VARCHAR4;-- := PO_TBL_VARCHAR4();
2983: -- SQL Why : This TLP row will be copied when creating translations
2984: -- SQL Join: org_id, req_template_name, req_template_line_num, language
2985: SELECT attribute_values_tlp_id
2986: BULK COLLECT INTO l_attr_values_tlp_id_list
2987: FROM PO_ATTRIBUTE_VALUES_TLP
2988: WHERE req_template_name = p_req_template_name
2989: AND req_template_line_num = p_req_template_line_num
2990: AND org_id = p_org_id
2991: AND p_req_template_line_num <> g_NOT_REQUIRED_ID
3019: -- language does already exist.
3020: -- SQL Why : To create translations in multiple languages
3021: -- SQL Join: attribute_values_tlp_id
3022: FORALL i IN 1 .. l_tlp_id_to_be_copied_list.COUNT
3023: INSERT INTO PO_ATTRIBUTE_VALUES_TLP TLP
3024: (
3025: attribute_values_tlp_id,
3026: description,
3027: language,
3199: program_update_date,
3200: last_updated_program
3201: )
3202: SELECT
3203: PO_ATTRIBUTE_VALUES_TLP_S.nextval,
3204: l_tlp_new_descriptions_list(i),
3205: l_tlp_language_list(i),
3206:
3207: -- ... ALL OTHER COLUMNS FROM TLP
3377: TLP1.program_application_id,
3378: TLP1.program_id,
3379: TLP1.program_update_date,
3380: d_mod -- last_updated_program
3381: FROM PO_ATTRIBUTE_VALUES_TLP TLP1
3382: WHERE TLP1.attribute_values_tlp_id = l_tlp_id_to_be_copied_list(i)
3383: AND NOT EXISTS
3384: (SELECT /*+ push_subq no_unnest */ /* Hint for performance Bug 9392614 */
3385: 'TLP row for this language already exists'
3382: WHERE TLP1.attribute_values_tlp_id = l_tlp_id_to_be_copied_list(i)
3383: AND NOT EXISTS
3384: (SELECT /*+ push_subq no_unnest */ /* Hint for performance Bug 9392614 */
3385: 'TLP row for this language already exists'
3386: FROM PO_ATTRIBUTE_VALUES_TLP TLP2
3387: WHERE TLP2.po_line_id = TLP1.po_line_id
3388: AND TLP2.req_template_name = TLP1.req_template_name
3389: AND TLP2.req_template_line_num = TLP1.req_template_line_num
3390: AND TLP2.org_id = TLP1.org_id
3434: p_req_template_line_num IN PO_REQEXPRESS_LINES_ALL.sequence_num%TYPE,
3435: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
3436: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
3437: p_org_id IN PO_LINES_ALL.org_id%TYPE,
3438: p_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE,
3439: -- Bug 7039409: Added new param p_manufacturer
3440: p_manufacturer IN PO_ATTRIBUTE_VALUES_TLP.manufacturer%TYPE
3441: )
3442: IS
3436: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
3437: p_org_id IN PO_LINES_ALL.org_id%TYPE,
3438: p_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE,
3439: -- Bug 7039409: Added new param p_manufacturer
3440: p_manufacturer IN PO_ATTRIBUTE_VALUES_TLP.manufacturer%TYPE
3441: )
3442: IS
3443: d_mod CONSTANT VARCHAR2(100) := D_create_default_attr_tlp;
3444: l_progress VARCHAR2(4);
3443: d_mod CONSTANT VARCHAR2(100) := D_create_default_attr_tlp;
3444: l_progress VARCHAR2(4);
3445:
3446: l_default_lang PO_HEADERS_ALL.created_language%TYPE;
3447: l_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE;
3448: l_long_description PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE;
3449: BEGIN
3450: l_progress := '010';
3451:
3444: l_progress VARCHAR2(4);
3445:
3446: l_default_lang PO_HEADERS_ALL.created_language%TYPE;
3447: l_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE;
3448: l_long_description PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE;
3449: BEGIN
3450: l_progress := '010';
3451:
3452: IF PO_LOG.d_proc THEN
3491: -- SQL What: Insert default rows for Attribute values TLP.
3492: -- This SQL will insert multiple rows, one for each installed lang.
3493: -- SQL Why : To create a default Attr TLP row
3494: -- SQL Join: po_line_id
3495: INSERT INTO PO_ATTRIBUTE_VALUES_TLP (
3496: attribute_values_tlp_id,
3497: po_line_id,
3498: req_template_name,
3499: req_template_line_num,
3516: program_update_date,
3517: last_updated_program
3518: )
3519: SELECT
3520: PO_ATTRIBUTE_VALUES_TLP_S.nextval,
3521: NVL(p_po_line_id,-2),
3522: NVL(p_req_template_name,'-2'),
3523: NVL(p_req_template_line_num,-2),
3524: NVL(p_ip_category_id,-2),
3541: d_mod -- last_updated_program
3542: FROM DUAL
3543: WHERE NOT EXISTS
3544: (SELECT 'TLP row for this language already exists'
3545: FROM PO_ATTRIBUTE_VALUES_TLP TLP2
3546: WHERE TLP2.po_line_id = p_po_line_id
3547: AND TLP2.req_template_name = p_req_template_name
3548: AND TLP2.req_template_line_num = p_req_template_line_num
3549: AND TLP2.org_id = p_org_id
3600: p_req_template_line_num IN PO_REQEXPRESS_LINES_ALL.sequence_num%TYPE,
3601: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
3602: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
3603: p_org_id IN PO_LINES_ALL.org_id%TYPE,
3604: p_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE
3605: )
3606: IS
3607: d_mod CONSTANT VARCHAR2(100) := D_create_default_attributes;
3608: l_progress VARCHAR2(4);
3608: l_progress VARCHAR2(4);
3609:
3610: l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
3611: -- Bug 7039409: Declared new variables
3612: l_manufacturer_part_num PO_ATTRIBUTE_VALUES.manufacturer_part_num%TYPE;
3613: l_manufacturer PO_ATTRIBUTE_VALUES_TLP.manufacturer%TYPE;
3614: l_lead_time PO_ATTRIBUTE_VALUES.lead_time%TYPE;
3615: BEGIN
3616: l_progress := '010';
3609:
3610: l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
3611: -- Bug 7039409: Declared new variables
3612: l_manufacturer_part_num PO_ATTRIBUTE_VALUES.manufacturer_part_num%TYPE;
3613: l_manufacturer PO_ATTRIBUTE_VALUES_TLP.manufacturer%TYPE;
3614: l_lead_time PO_ATTRIBUTE_VALUES.lead_time%TYPE;
3615: BEGIN
3616: l_progress := '010';
3617:
3610: l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
3611: -- Bug 7039409: Declared new variables
3612: l_manufacturer_part_num PO_ATTRIBUTE_VALUES.manufacturer_part_num%TYPE;
3613: l_manufacturer PO_ATTRIBUTE_VALUES_TLP.manufacturer%TYPE;
3614: l_lead_time PO_ATTRIBUTE_VALUES.lead_time%TYPE;
3615: BEGIN
3616: l_progress := '010';
3617:
3618: IF PO_LOG.d_proc THEN
3662: l_progress := '040';
3663: -- SQL What: Insert a new row for Attribute values
3664: -- SQL Why : To create a default Attr row
3665: -- SQL Join: none
3666: INSERT INTO PO_ATTRIBUTE_VALUES (
3667: attribute_values_id,
3668: po_line_id,
3669: req_template_name,
3670: req_template_line_num,
3685: program_update_date,
3686: last_updated_program
3687: )
3688: SELECT
3689: PO_ATTRIBUTE_VALUES_S.nextval,
3690: NVL(p_po_line_id,-2),
3691: NVL(p_req_template_name,'-2'),
3692: NVL(p_req_template_line_num,-2),
3693: NVL(p_ip_category_id,-2),
3708: d_mod -- last_updated_program
3709: FROM DUAL
3710: WHERE NOT EXISTS
3711: (SELECT 'Attribute row already exists'
3712: FROM PO_ATTRIBUTE_VALUES POATR
3713: WHERE POATR.po_line_id = p_po_line_id
3714: AND POATR.req_template_name = p_req_template_name
3715: AND POATR.req_template_line_num = p_req_template_line_num
3716: AND POATR.org_id = p_org_id);
3742: (
3743: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
3744: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
3745: p_org_id IN PO_LINES_ALL.org_id%TYPE,
3746: p_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE,
3747: p_organization_id IN NUMBER,
3748: p_master_organization_id IN NUMBER
3749: )
3750: IS
3749: )
3750: IS
3751: d_mod CONSTANT VARCHAR2(100) := D_create_default_attributes_MI;
3752: l_progress VARCHAR2(4);
3753: l_manufacturer_part_num PO_ATTRIBUTE_VALUES.MANUFACTURER_PART_NUM%TYPE;
3754: l_proc_lead_time PO_ATTRIBUTE_VALUES.LEAD_TIME%TYPE;
3755: l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
3756: BEGIN
3757: l_progress := '010';
3750: IS
3751: d_mod CONSTANT VARCHAR2(100) := D_create_default_attributes_MI;
3752: l_progress VARCHAR2(4);
3753: l_manufacturer_part_num PO_ATTRIBUTE_VALUES.MANUFACTURER_PART_NUM%TYPE;
3754: l_proc_lead_time PO_ATTRIBUTE_VALUES.LEAD_TIME%TYPE;
3755: l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
3756: BEGIN
3757: l_progress := '010';
3758:
3766: END IF;
3767:
3768: l_progress := '020';
3769:
3770: -- list down the base descriptors in PO_ATTRIBUTE_VALUES
3771: -- LEAD_TIME AVAILABILITY UNSPSC MANUFACTURER_PART_NUM
3772: -- PICTURE THUMBNAIL_IMAGE SUPPLIER_URL MANUFACTURER_URL
3773: -- ATTACHMENT_URL
3774: BEGIN
3803: l_progress := '030';
3804: -- SQL What: Insert a new row for Attribute values
3805: -- SQL Why : To create a default Attr row
3806: -- SQL Join: none
3807: INSERT INTO PO_ATTRIBUTE_VALUES (
3808: attribute_values_id,
3809: po_line_id,
3810: req_template_name,
3811: req_template_line_num,
3826: program_update_date,
3827: last_updated_program
3828: )
3829: SELECT
3830: PO_ATTRIBUTE_VALUES_S.nextval,
3831: -2,
3832: '-2',
3833: -2,
3834: NVL(p_ip_category_id,-2),
3849: d_mod -- last_updated_program
3850: FROM DUAL
3851: WHERE NOT EXISTS
3852: (SELECT 'Attribute row already exists'
3853: FROM PO_ATTRIBUTE_VALUES POATR
3854: WHERE POATR.inventory_item_id = p_inventory_item_id
3855: AND POATR.org_id = p_org_id
3856: AND POATR.po_line_id = -2
3857: AND POATR.req_template_name = '-2'
3872: PROCEDURE wipeout_category_attributes_MI
3873: (
3874: p_org_id IN PO_LINES_ALL.org_id%TYPE,
3875: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
3876: p_inventory_item_id IN PO_ATTRIBUTE_VALUES_TLP.inventory_item_id%TYPE,
3877: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
3878: p_item_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE
3879: )
3880: IS
3873: (
3874: p_org_id IN PO_LINES_ALL.org_id%TYPE,
3875: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
3876: p_inventory_item_id IN PO_ATTRIBUTE_VALUES_TLP.inventory_item_id%TYPE,
3877: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
3878: p_item_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE
3879: )
3880: IS
3881: d_mod CONSTANT VARCHAR2(100) := D_wipeout_category_attributes||'_MI';
3874: p_org_id IN PO_LINES_ALL.org_id%TYPE,
3875: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
3876: p_inventory_item_id IN PO_ATTRIBUTE_VALUES_TLP.inventory_item_id%TYPE,
3877: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
3878: p_item_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE
3879: )
3880: IS
3881: d_mod CONSTANT VARCHAR2(100) := D_wipeout_category_attributes||'_MI';
3882: l_progress VARCHAR2(4);
3894: l_progress := '020';
3895: -- SQL What: Wipeout category based attributes from AttributeValues table
3896: -- SQL Why : Because the category was changed
3897: -- SQL Join: po_line_id, req_template_name, req_template_line_num, org_id
3898: UPDATE PO_ATTRIBUTE_VALUES
3899: SET
3900: NUM_CAT_ATTRIBUTE1 = NULL,
3901: NUM_CAT_ATTRIBUTE2 = NULL,
3902: NUM_CAT_ATTRIBUTE3 = NULL,
4019: l_progress := '030';
4020: -- SQL What: Wipeout category based attributes from TLP table for all Langs
4021: -- SQL Why : Because the category was changed
4022: -- SQL Join: po_line_id, req_template_name, req_template_line_num, org_id
4023: UPDATE PO_ATTRIBUTE_VALUES_TLP
4024: SET
4025: TL_TEXT_CAT_ATTRIBUTE1 = NULL,
4026: TL_TEXT_CAT_ATTRIBUTE2 = NULL,
4027: TL_TEXT_CAT_ATTRIBUTE3 = NULL,
4103: PROCEDURE update_attributes_MI
4104: (
4105: p_org_id IN PO_LINES_ALL.org_id%TYPE,
4106: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
4107: p_inventory_item_id IN PO_ATTRIBUTE_VALUES_TLP.inventory_item_id%TYPE,
4108: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
4109: p_item_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE,
4110: p_long_description IN PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE,
4111: p_organization_id IN NUMBER,
4104: (
4105: p_org_id IN PO_LINES_ALL.org_id%TYPE,
4106: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
4107: p_inventory_item_id IN PO_ATTRIBUTE_VALUES_TLP.inventory_item_id%TYPE,
4108: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
4109: p_item_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE,
4110: p_long_description IN PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE,
4111: p_organization_id IN NUMBER,
4112: p_master_organization_id IN NUMBER
4105: p_org_id IN PO_LINES_ALL.org_id%TYPE,
4106: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
4107: p_inventory_item_id IN PO_ATTRIBUTE_VALUES_TLP.inventory_item_id%TYPE,
4108: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
4109: p_item_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE,
4110: p_long_description IN PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE,
4111: p_organization_id IN NUMBER,
4112: p_master_organization_id IN NUMBER
4113: )
4106: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
4107: p_inventory_item_id IN PO_ATTRIBUTE_VALUES_TLP.inventory_item_id%TYPE,
4108: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
4109: p_item_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE,
4110: p_long_description IN PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE,
4111: p_organization_id IN NUMBER,
4112: p_master_organization_id IN NUMBER
4113: )
4114: IS
4116: l_progress VARCHAR2(4);
4117:
4118: l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
4119: l_orig_ip_category_id PO_LINES_ALL.ip_category_id%TYPE;
4120: l_orig_item_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE;
4121: l_manufacturer_part_num PO_ATTRIBUTE_VALUES.MANUFACTURER_PART_NUM%TYPE;
4122: l_orig_long_desc PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE;
4123: l_new_long_description PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE := nvl(p_long_description,' ');
4124: l_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
4117:
4118: l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
4119: l_orig_ip_category_id PO_LINES_ALL.ip_category_id%TYPE;
4120: l_orig_item_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE;
4121: l_manufacturer_part_num PO_ATTRIBUTE_VALUES.MANUFACTURER_PART_NUM%TYPE;
4122: l_orig_long_desc PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE;
4123: l_new_long_description PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE := nvl(p_long_description,' ');
4124: l_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
4125: l_proc_lead_time PO_ATTRIBUTE_VALUES.LEAD_TIME%TYPE;
4118: l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
4119: l_orig_ip_category_id PO_LINES_ALL.ip_category_id%TYPE;
4120: l_orig_item_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE;
4121: l_manufacturer_part_num PO_ATTRIBUTE_VALUES.MANUFACTURER_PART_NUM%TYPE;
4122: l_orig_long_desc PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE;
4123: l_new_long_description PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE := nvl(p_long_description,' ');
4124: l_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
4125: l_proc_lead_time PO_ATTRIBUTE_VALUES.LEAD_TIME%TYPE;
4126: l_orig_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
4119: l_orig_ip_category_id PO_LINES_ALL.ip_category_id%TYPE;
4120: l_orig_item_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE;
4121: l_manufacturer_part_num PO_ATTRIBUTE_VALUES.MANUFACTURER_PART_NUM%TYPE;
4122: l_orig_long_desc PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE;
4123: l_new_long_description PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE := nvl(p_long_description,' ');
4124: l_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
4125: l_proc_lead_time PO_ATTRIBUTE_VALUES.LEAD_TIME%TYPE;
4126: l_orig_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
4127: l_inventory_org_id PO_LINES_ALL.org_id%TYPE;
4120: l_orig_item_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE;
4121: l_manufacturer_part_num PO_ATTRIBUTE_VALUES.MANUFACTURER_PART_NUM%TYPE;
4122: l_orig_long_desc PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE;
4123: l_new_long_description PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE := nvl(p_long_description,' ');
4124: l_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
4125: l_proc_lead_time PO_ATTRIBUTE_VALUES.LEAD_TIME%TYPE;
4126: l_orig_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
4127: l_inventory_org_id PO_LINES_ALL.org_id%TYPE;
4128: l_rec_tlp_for_lang VARCHAR2(100);
4121: l_manufacturer_part_num PO_ATTRIBUTE_VALUES.MANUFACTURER_PART_NUM%TYPE;
4122: l_orig_long_desc PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE;
4123: l_new_long_description PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE := nvl(p_long_description,' ');
4124: l_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
4125: l_proc_lead_time PO_ATTRIBUTE_VALUES.LEAD_TIME%TYPE;
4126: l_orig_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
4127: l_inventory_org_id PO_LINES_ALL.org_id%TYPE;
4128: l_rec_tlp_for_lang VARCHAR2(100);
4129:
4122: l_orig_long_desc PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE;
4123: l_new_long_description PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE := nvl(p_long_description,' ');
4124: l_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
4125: l_proc_lead_time PO_ATTRIBUTE_VALUES.LEAD_TIME%TYPE;
4126: l_orig_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
4127: l_inventory_org_id PO_LINES_ALL.org_id%TYPE;
4128: l_rec_tlp_for_lang VARCHAR2(100);
4129:
4130: BEGIN
4151: begin
4152:
4153: SELECT ip_category_id
4154: INTO l_orig_ip_category_id
4155: FROM PO_ATTRIBUTE_VALUES
4156: WHERE po_line_id = -2
4157: AND req_template_name = '-2'
4158: AND req_template_line_num = -2
4159: AND inventory_item_id = p_inventory_item_id
4179:
4180: l_progress := '050';
4181: -- If ip_category_id has changed, then NULL out the category based attributes
4182: -- i.e. all attribute columns that have 'CAT' in the name.
4183: -- the PO_ATTRIBUTE_VALUES is foing to be updated there for
4184: -- query the latest saved attributes in mtl
4185: -- list down the base descriptors in PO_ATTRIBUTE_VALUES
4186: -- LEAD_TIME AVAILABILITY UNSPSC MANUFACTURER_PART_NUM
4187: -- PICTURE THUMBNAIL_IMAGE SUPPLIER_URL MANUFACTURER_URL
4181: -- If ip_category_id has changed, then NULL out the category based attributes
4182: -- i.e. all attribute columns that have 'CAT' in the name.
4183: -- the PO_ATTRIBUTE_VALUES is foing to be updated there for
4184: -- query the latest saved attributes in mtl
4185: -- list down the base descriptors in PO_ATTRIBUTE_VALUES
4186: -- LEAD_TIME AVAILABILITY UNSPSC MANUFACTURER_PART_NUM
4187: -- PICTURE THUMBNAIL_IMAGE SUPPLIER_URL MANUFACTURER_URL
4188: -- ATTACHMENT_URL
4189: BEGIN
4239:
4240: -- SQL What: Update Attribute values
4241: -- SQL Why : To keep in synch with the Line level values
4242: -- SQL Join: po_line_id, req_template_name, req_template_line_num, org_id
4243: UPDATE PO_ATTRIBUTE_VALUES
4244: SET ip_category_id = p_ip_category_id,
4245: MANUFACTURER_PART_NUM = l_manufacturer_part_num,
4246: LEAD_TIME = l_proc_lead_time,
4247: -- WHO columns
4282:
4283: BEGIN
4284: SELECT 'TLP row for this language already exists'
4285: INTO l_rec_tlp_for_lang
4286: FROM PO_ATTRIBUTE_VALUES_TLP TLP2
4287: WHERE TLP2.po_line_id = -2
4288: AND TLP2.req_template_name = '-2'
4289: AND TLP2.req_template_line_num = -2
4290: AND TLP2.org_id = p_org_id
4295: l_rec_tlp_for_lang:='';
4296: END;
4297:
4298: IF (l_rec_tlp_for_lang IS NULL OR l_rec_tlp_for_lang = '' ) THEN
4299: IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,l_progress,'no records hence insert into PO_ATTRIBUTE_VALUES_TLP ' ); END IF;
4300: l_progress := '090';
4301:
4302: INSERT INTO PO_ATTRIBUTE_VALUES_TLP TLP
4303: (
4298: IF (l_rec_tlp_for_lang IS NULL OR l_rec_tlp_for_lang = '' ) THEN
4299: IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,l_progress,'no records hence insert into PO_ATTRIBUTE_VALUES_TLP ' ); END IF;
4300: l_progress := '090';
4301:
4302: INSERT INTO PO_ATTRIBUTE_VALUES_TLP TLP
4303: (
4304: attribute_values_tlp_id,
4305: description,
4306: language,
4323: program_update_date,
4324: last_updated_program
4325: )
4326: SELECT
4327: PO_ATTRIBUTE_VALUES_TLP_S.nextval,
4328: p_item_description,
4329: p_language,
4330: -2,
4331: '-2',
4347: d_mod -- last_updated_program
4348: FROM dual;
4349:
4350: ELSE
4351: IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,l_progress,' records present hence update PO_ATTRIBUTE_VALUES_TLP ' || l_rec_tlp_for_lang); END IF;
4352: l_progress := '095';
4353: SELECT description,Nvl(LONG_DESCRIPTION,''),Nvl(MANUFACTURER,'')
4354: INTO l_orig_item_description,l_orig_long_desc,l_orig_manufacturer
4355: FROM PO_ATTRIBUTE_VALUES_TLP
4351: IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,l_progress,' records present hence update PO_ATTRIBUTE_VALUES_TLP ' || l_rec_tlp_for_lang); END IF;
4352: l_progress := '095';
4353: SELECT description,Nvl(LONG_DESCRIPTION,''),Nvl(MANUFACTURER,'')
4354: INTO l_orig_item_description,l_orig_long_desc,l_orig_manufacturer
4355: FROM PO_ATTRIBUTE_VALUES_TLP
4356: WHERE po_line_id = -2
4357: AND req_template_name = '-2'
4358: AND req_template_line_num = -2
4359: AND inventory_item_id = p_inventory_item_id
4374: -- SQL Join: po_line_id, req_template_name, req_template_line_num, org_id, language
4375:
4376:
4377:
4378: UPDATE PO_ATTRIBUTE_VALUES_TLP
4379: SET ip_category_id = p_ip_category_id,
4380: description = p_item_description,
4381: manufacturer = l_manufacturer,
4382: long_description = l_new_long_description,
4454: d_mod CONSTANT VARCHAR2(100) := D_gen_draft_line_translations;
4455: l_progress VARCHAR2(4);
4456:
4457: l_language_codes PO_TBL_NUMBER;
4458: l_tlp_row PO_ATTRIBUTE_VALUES_TLP%rowtype;
4459: l_po_line_id_list PO_TBL_NUMBER;
4460: l_tlp_id_list PO_TBL_NUMBER;
4461: BEGIN
4462: l_progress := '010';
4476: -- SQL Join: draft_id, po_header_id, po_line_id, language
4477: SELECT POLD.po_line_id, TLP.attribute_values_tlp_id
4478: BULK COLLECT INTO l_po_line_id_list, l_tlp_id_list
4479: FROM PO_LINES_DRAFT_ALL POLD,
4480: PO_ATTRIBUTE_VALUES_TLP TLP,
4481: PO_HEADERS_ALL POH
4482: WHERE POLD.draft_id = p_draft_id
4483: AND NVL(POLD.change_accepted_flag, 'Y') = 'Y'
4484: AND POLD.po_line_id = TLP.po_line_id
4553: l_progress := '020';
4554: -- SQL What: Wipeout category based attributes from AttributeValues table
4555: -- SQL Why : Because the category was changed
4556: -- SQL Join: po_line_id, req_template_name, req_template_line_num, org_id
4557: UPDATE PO_ATTRIBUTE_VALUES
4558: SET
4559: NUM_CAT_ATTRIBUTE1 = NULL,
4560: NUM_CAT_ATTRIBUTE2 = NULL,
4561: NUM_CAT_ATTRIBUTE3 = NULL,
4677: l_progress := '030';
4678: -- SQL What: Wipeout category based attributes from TLP table for all Langs
4679: -- SQL Why : Because the category was changed
4680: -- SQL Join: po_line_id, req_template_name, req_template_line_num, org_id
4681: UPDATE PO_ATTRIBUTE_VALUES_TLP
4682: SET
4683: TL_TEXT_CAT_ATTRIBUTE1 = NULL,
4684: TL_TEXT_CAT_ATTRIBUTE2 = NULL,
4685: TL_TEXT_CAT_ATTRIBUTE3 = NULL,
4789: p_req_template_name IN PO_REQEXPRESS_LINES_ALL.express_name%TYPE,
4790: p_req_template_line_num IN PO_REQEXPRESS_LINES_ALL.sequence_num%TYPE,
4791: p_org_id IN PO_LINES_ALL.org_id%TYPE,
4792: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
4793: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
4794: p_item_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE
4795: )
4796: IS
4797: d_mod CONSTANT VARCHAR2(100) := D_update_attributes;
4790: p_req_template_line_num IN PO_REQEXPRESS_LINES_ALL.sequence_num%TYPE,
4791: p_org_id IN PO_LINES_ALL.org_id%TYPE,
4792: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
4793: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
4794: p_item_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE
4795: )
4796: IS
4797: d_mod CONSTANT VARCHAR2(100) := D_update_attributes;
4798: l_progress VARCHAR2(4);
4798: l_progress VARCHAR2(4);
4799:
4800: l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
4801: l_orig_ip_category_id PO_LINES_ALL.ip_category_id%TYPE;
4802: l_orig_item_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE;
4803: l_orig_long_description PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE;
4804: l_long_description MTL_SYSTEM_ITEMS_TL.long_description%TYPE;
4805: l_lead_time MTL_SYSTEM_ITEMS_B.full_lead_time%TYPE;
4806: l_orig_lead_time PO_ATTRIBUTE_VALUES.lead_time%TYPE;
4799:
4800: l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
4801: l_orig_ip_category_id PO_LINES_ALL.ip_category_id%TYPE;
4802: l_orig_item_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE;
4803: l_orig_long_description PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE;
4804: l_long_description MTL_SYSTEM_ITEMS_TL.long_description%TYPE;
4805: l_lead_time MTL_SYSTEM_ITEMS_B.full_lead_time%TYPE;
4806: l_orig_lead_time PO_ATTRIBUTE_VALUES.lead_time%TYPE;
4807: l_inventory_item_id PO_ATTRIBUTE_VALUES_TLP.inventory_item_id%TYPE;
4802: l_orig_item_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE;
4803: l_orig_long_description PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE;
4804: l_long_description MTL_SYSTEM_ITEMS_TL.long_description%TYPE;
4805: l_lead_time MTL_SYSTEM_ITEMS_B.full_lead_time%TYPE;
4806: l_orig_lead_time PO_ATTRIBUTE_VALUES.lead_time%TYPE;
4807: l_inventory_item_id PO_ATTRIBUTE_VALUES_TLP.inventory_item_id%TYPE;
4808: BEGIN
4809: l_progress := '010';
4810:
4803: l_orig_long_description PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE;
4804: l_long_description MTL_SYSTEM_ITEMS_TL.long_description%TYPE;
4805: l_lead_time MTL_SYSTEM_ITEMS_B.full_lead_time%TYPE;
4806: l_orig_lead_time PO_ATTRIBUTE_VALUES.lead_time%TYPE;
4807: l_inventory_item_id PO_ATTRIBUTE_VALUES_TLP.inventory_item_id%TYPE;
4808: BEGIN
4809: l_progress := '010';
4810:
4811: IF PO_LOG.d_proc THEN
4849: -- name.
4850: -- SQL Join: po_line_id, req_template_name, req_template_line_num, org_id
4851: SELECT ip_category_id,inventory_item_id,lead_time
4852: INTO l_orig_ip_category_id,l_inventory_item_id,l_orig_lead_time
4853: FROM PO_ATTRIBUTE_VALUES
4854: WHERE po_line_id = NVL(p_po_line_id, -2)
4855: AND req_template_name = NVL(p_req_template_name, '-2')
4856: AND req_template_line_num = NVL(p_req_template_line_num, -2)
4857: AND org_id = p_org_id;
4875: l_progress := '070';
4876: -- SQL What: Update Attribute values
4877: -- SQL Why : To keep in synch with the Line level values
4878: -- SQL Join: po_line_id, req_template_name, req_template_line_num, org_id
4879: UPDATE PO_ATTRIBUTE_VALUES
4880: SET ip_category_id = p_ip_category_id,
4881: -- WHO columns
4882: LAST_UPDATE_LOGIN = FND_GLOBAL.login_id,
4883: LAST_UPDATED_BY = FND_GLOBAL.user_id,
4903: l_progress := '080';
4904:
4905: --
4906: -- Handle NO_DATA_FOUND exception, as for one time item there can be only one
4907: -- entry in PO_ATTRIBUTE_VALUES_TLP, corresponding to the base lang,
4908: -- if profile "POR: Load One-Time Items in All Languages" is set to No.
4909: BEGIN
4910: -- SQL What: Check if item_description has changed
4911: -- SQL Why : No need to update TLP row if item_description and
4912: -- ip_category_id have not changed.
4913: -- SQL Join: po_line_id, req_template_name, req_template_line_num, org_id
4914: SELECT description,long_description
4915: INTO l_orig_item_description,l_orig_long_description
4916: FROM PO_ATTRIBUTE_VALUES_TLP
4917: WHERE po_line_id = NVL(p_po_line_id, -2)
4918: AND req_template_name = NVL(p_req_template_name, '-2')
4919: AND req_template_line_num = NVL(p_req_template_line_num, -2)
4920: AND org_id = p_org_id
4935: (p_item_description <> l_orig_item_description) ) THEN
4936: -- SQL What: Update Attribute TLP values
4937: -- SQL Why : To keep in synch with the Line level values
4938: -- SQL Join: po_line_id, req_template_name, req_template_line_num, org_id, language
4939: UPDATE PO_ATTRIBUTE_VALUES_TLP
4940: SET ip_category_id = p_ip_category_id,
4941: description = p_item_description,
4942: -- WHO columns
4943: LAST_UPDATE_LOGIN = FND_GLOBAL.login_id,
4963:
4964: --
4965: BEGIN
4966: -- SQL What: Gets the long_descripton from master items table
4967: -- SQL Why : Need to update long description value of Po_attribute_values
4968: -- if it does not match with master items long_description .
4969: -- SQL Join:
4970: SELECT long_description
4971: INTO l_long_description
4985:
4986: -- SQL What: Update Attribute TLP values
4987: -- SQL Why : To keep in synch with the Line level values
4988: -- SQL Join: po_line_id, req_template_name, req_template_line_num, org_id, language
4989: UPDATE PO_ATTRIBUTE_VALUES_TLP
4990: SET long_description = l_long_description,
4991: -- WHO columns
4992: LAST_UPDATE_LOGIN = FND_GLOBAL.login_id,
4993: LAST_UPDATED_BY = FND_GLOBAL.user_id,
5010:
5011: l_progress := '120';
5012: BEGIN
5013: -- SQL What: Gets the lead time from mtl_system_items_b table
5014: -- SQL Why : Need to update lead_time value of Po_attribute_values
5015: -- if it does not match withmtl_system_items_b lead_time.
5016: -- SQL Join:
5017: SELECT full_lead_time
5018: INTO l_lead_time
5031: or (l_orig_lead_time is null and l_lead_time is not null)) THEN
5032: l_progress := '060';-- SQL What: Update Attribute values
5033: -- SQL Why : To keep in synch with the Line level values
5034: -- SQL Join: po_line_id, req_template_name, req_template_line_num, org_id
5035: UPDATE PO_ATTRIBUTE_VALUES
5036: SET lead_time = l_lead_time,
5037: -- WHO columns
5038: LAST_UPDATE_LOGIN = FND_GLOBAL.login_id,
5039: LAST_UPDATED_BY = FND_GLOBAL.user_id,
5106: -- SQL What: Insert a new row for Attribute values
5107: -- SQL Why : To copy the Attr from old doc to new doc
5108: -- SQL Join: po_line_id
5109:
5110: INSERT INTO PO_ATTRIBUTE_VALUES (
5111: attribute_values_id,
5112: po_line_id,
5113: req_template_name,
5114: req_template_line_num,
5435: program_update_date,
5436: last_updated_program
5437: )
5438: SELECT
5439: PO_ATTRIBUTE_VALUES_S.nextval,
5440: p_new_po_line_id,
5441: POATR.req_template_name,
5442: POATR.req_template_line_num,
5443: POATR.ip_category_id,
5761: POATR.program_application_id,
5762: POATR.program_id,
5763: POATR.program_update_date,
5764: d_mod -- last_updated_program
5765: FROM PO_ATTRIBUTE_VALUES POATR
5766: WHERE POATR.po_line_id = p_orig_po_line_id
5767: AND NOT EXISTS
5768: (SELECT /*+ push_subq no_unnest */ /* Hint for performance Bug 9392614 */
5769: 'ATTR row already exists'
5766: WHERE POATR.po_line_id = p_orig_po_line_id
5767: AND NOT EXISTS
5768: (SELECT /*+ push_subq no_unnest */ /* Hint for performance Bug 9392614 */
5769: 'ATTR row already exists'
5770: FROM PO_ATTRIBUTE_VALUES ATR2
5771: WHERE ATR2.po_line_id = p_new_po_line_id
5772: AND ATR2.req_template_name = POATR.req_template_name
5773: AND ATR2.req_template_line_num = POATR.req_template_line_num
5774: AND ATR2.org_id = POATR.org_id);
5779: -- SQL What: Insert new rows for Attribute values TLP.
5780: -- This SQL will enter multiple rows, one for each installed lang.
5781: -- SQL Why : To copy the TLP from old doc to new doc
5782: -- SQL Join: po_line_id
5783: INSERT INTO PO_ATTRIBUTE_VALUES_TLP (
5784: attribute_values_tlp_id,
5785: po_line_id,
5786: req_template_name,
5787: req_template_line_num,
5955: program_update_date,
5956: last_updated_program
5957: )
5958: SELECT
5959: PO_ATTRIBUTE_VALUES_TLP_S.nextval,
5960: p_new_po_line_id,
5961: POTLP.req_template_name,
5962: POTLP.req_template_line_num,
5963: POTLP.ip_category_id,
6128: POTLP.program_application_id,
6129: POTLP.program_id,
6130: POTLP.program_update_date,
6131: d_mod -- last_updated_program
6132: FROM PO_ATTRIBUTE_VALUES_TLP POTLP
6133: WHERE POTLP.po_line_id = p_orig_po_line_id
6134: AND NOT EXISTS
6135: (SELECT /*+ push_subq no_unnest */ /* Hint for performance Bug 9392614 */
6136: 'TLP row for this language already exists'
6133: WHERE POTLP.po_line_id = p_orig_po_line_id
6134: AND NOT EXISTS
6135: (SELECT /*+ push_subq no_unnest */ /* Hint for performance Bug 9392614 */
6136: 'TLP row for this language already exists'
6137: FROM PO_ATTRIBUTE_VALUES_TLP TLP2
6138: WHERE TLP2.po_line_id = p_new_po_line_id
6139: AND TLP2.req_template_name = POTLP.req_template_name
6140: AND TLP2.req_template_line_num = POTLP.req_template_line_num
6141: AND TLP2.org_id = POTLP.org_id
6261:
6262: -- SQL What: Delete Attribute Values
6263: -- SQL Why : as required by this procedure
6264: -- SQL Join: po_line_id, req_template_name, req_template_line_num, org_id
6265: DELETE FROM PO_ATTRIBUTE_VALUES
6266: WHERE po_line_id = NVL(p_po_line_id, -2)
6267: AND req_template_name = NVL(p_req_template_name, '-2')
6268: AND req_template_line_num = NVL(p_req_template_line_num, -2)
6269: AND org_id = NVL(p_org_id, PO_MOAC_UTILS_PVT.get_current_org_id);
6273:
6274: -- SQL What: Delete Attribute Values TLP
6275: -- SQL Why : as required by this procedure
6276: -- SQL Join: po_line_id, req_template_name, req_template_line_num, org_id
6277: DELETE FROM PO_ATTRIBUTE_VALUES_TLP
6278: WHERE po_line_id = NVL(p_po_line_id, -2)
6279: AND req_template_name = NVL(p_req_template_name, '-2')
6280: AND req_template_line_num = NVL(p_req_template_line_num, -2)
6281: AND org_id = NVL(p_org_id, PO_MOAC_UTILS_PVT.get_current_org_id);
6332: IF (p_doc_type IN ('BLANKET', 'QUOTATION')) THEN
6333: -- SQL What: Delete Attribute Values for all lines in a PO Header
6334: -- SQL Why : as required by this procedure
6335: -- SQL Join: po_line_id, po_header_id
6336: DELETE FROM PO_ATTRIBUTE_VALUES POATR
6337: WHERE EXISTS
6338: (SELECT 'All PO Lines for the given Header'
6339: FROM PO_LINES_ALL POL
6340: WHERE POL.po_header_id = p_po_header_id
6346:
6347: -- SQL What: Delete Attribute Values TLP for all lines in a PO Header
6348: -- SQL Why : as required by this procedure
6349: -- SQL Join: po_line_id, po_header_id
6350: DELETE FROM PO_ATTRIBUTE_VALUES_TLP POTLP
6351: WHERE EXISTS
6352: (SELECT 'All PO Lines for the given Header'
6353: FROM PO_LINES_ALL POL
6354: WHERE POL.po_header_id = p_po_header_id
6420: (
6421: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
6422: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
6423: p_org_id IN PO_LINES_ALL.org_id%TYPE,
6424: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
6425: p_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE,
6426: p_long_description IN PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE,
6427: p_organization_id IN NUMBER,
6428: p_master_organization_id IN NUMBER
6421: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
6422: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
6423: p_org_id IN PO_LINES_ALL.org_id%TYPE,
6424: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
6425: p_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE,
6426: p_long_description IN PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE,
6427: p_organization_id IN NUMBER,
6428: p_master_organization_id IN NUMBER
6429: )
6422: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
6423: p_org_id IN PO_LINES_ALL.org_id%TYPE,
6424: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
6425: p_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE,
6426: p_long_description IN PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE,
6427: p_organization_id IN NUMBER,
6428: p_master_organization_id IN NUMBER
6429: )
6430: IS
6429: )
6430: IS
6431: d_mod CONSTANT VARCHAR2(100) := D_create_attributes_tlp_MI;
6432: l_progress VARCHAR2(4);
6433: l_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE := nvl(p_description,' ');
6434: l_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
6435: l_long_description PO_ATTRIBUTE_VALUES_TLP.LONG_DESCRIPTION%TYPE := nvl(p_long_description,' ');
6436:
6437: BEGIN
6430: IS
6431: d_mod CONSTANT VARCHAR2(100) := D_create_attributes_tlp_MI;
6432: l_progress VARCHAR2(4);
6433: l_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE := nvl(p_description,' ');
6434: l_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
6435: l_long_description PO_ATTRIBUTE_VALUES_TLP.LONG_DESCRIPTION%TYPE := nvl(p_long_description,' ');
6436:
6437: BEGIN
6438: l_progress := '010';
6431: d_mod CONSTANT VARCHAR2(100) := D_create_attributes_tlp_MI;
6432: l_progress VARCHAR2(4);
6433: l_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE := nvl(p_description,' ');
6434: l_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
6435: l_long_description PO_ATTRIBUTE_VALUES_TLP.LONG_DESCRIPTION%TYPE := nvl(p_long_description,' ');
6436:
6437: BEGIN
6438: l_progress := '010';
6439:
6451: END IF;
6452:
6453: l_progress := '030';
6454:
6455: -- list down the base descriptors in PO_ATTRIBUTE_VALUES_TLP
6456: -- MANUFACTURER COMMENTS ALIAS LONG_DESCRIPTION
6457: Begin
6458: SELECT MANUFACTURER_NAME
6459: INTO l_manufacturer
6473: -- SQL What: Insert default rows for Attribute values TLP.
6474: -- This SQL will insert multiple rows, one for each installed lang.
6475: -- SQL Why : To create a default Attr TLP row
6476: -- SQL Join: po_line_id
6477: INSERT INTO PO_ATTRIBUTE_VALUES_TLP (
6478: attribute_values_tlp_id,
6479: po_line_id,
6480: req_template_name,
6481: req_template_line_num,
6498: program_update_date,
6499: last_updated_program
6500: )
6501: SELECT
6502: PO_ATTRIBUTE_VALUES_TLP_S.nextval,
6503: -2,
6504: '-2',
6505: -2,
6506: NVL(p_ip_category_id,-2),
6523: d_mod -- last_updated_program
6524: FROM DUAL
6525: WHERE NOT EXISTS
6526: (SELECT 'TLP row for this language already exists'
6527: FROM PO_ATTRIBUTE_VALUES_TLP TLP2
6528: WHERE TLP2.inventory_item_id = p_inventory_item_id
6529: AND TLP2.org_id = p_org_id
6530: AND TLP2.language = p_language
6531: AND TLP2.po_line_id = -2
6553: -- None
6554: --Locks:
6555: -- None.
6556: --Function:
6557: -- To derive item attributes to be populated in PO_ATTRIBUTE_VALUES.
6558: --
6559: --Parameters:
6560: --IN:
6561: -- p_inventory_item_id
6567: --------------------------------------------------------------------------------
6568: PROCEDURE get_item_attributes_values
6569: (
6570: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
6571: p_manufacturer_part_num OUT NOCOPY PO_ATTRIBUTE_VALUES.manufacturer_part_num%TYPE,
6572: p_manufacturer OUT NOCOPY PO_ATTRIBUTE_VALUES_TLP.manufacturer%TYPE,
6573: p_lead_time OUT NOCOPY PO_ATTRIBUTE_VALUES.lead_time%TYPE
6574: )
6575: IS
6568: PROCEDURE get_item_attributes_values
6569: (
6570: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
6571: p_manufacturer_part_num OUT NOCOPY PO_ATTRIBUTE_VALUES.manufacturer_part_num%TYPE,
6572: p_manufacturer OUT NOCOPY PO_ATTRIBUTE_VALUES_TLP.manufacturer%TYPE,
6573: p_lead_time OUT NOCOPY PO_ATTRIBUTE_VALUES.lead_time%TYPE
6574: )
6575: IS
6576: l_manufacturer_id po_requisition_lines_All.MANUFACTURER_ID%TYPE;
6569: (
6570: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
6571: p_manufacturer_part_num OUT NOCOPY PO_ATTRIBUTE_VALUES.manufacturer_part_num%TYPE,
6572: p_manufacturer OUT NOCOPY PO_ATTRIBUTE_VALUES_TLP.manufacturer%TYPE,
6573: p_lead_time OUT NOCOPY PO_ATTRIBUTE_VALUES.lead_time%TYPE
6574: )
6575: IS
6576: l_manufacturer_id po_requisition_lines_All.MANUFACTURER_ID%TYPE;
6577: BEGIN
6589: -- None
6590: --Locks:
6591: -- None.
6592: --Function:
6593: -- To derive item attributes to be populated in PO_ATTRIBUTE_VALUES.
6594: --
6595: --Parameters:
6596: --IN:
6597: -- p_inventory_item_id
6604: --------------------------------------------------------------------------------
6605: PROCEDURE get_item_attributes_values
6606: (
6607: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
6608: p_manufacturer_part_num OUT NOCOPY PO_ATTRIBUTE_VALUES.manufacturer_part_num%TYPE,
6609: p_manufacturer OUT NOCOPY PO_ATTRIBUTE_VALUES_TLP.manufacturer%TYPE,
6610: p_lead_time OUT NOCOPY PO_ATTRIBUTE_VALUES.lead_time%TYPE,
6611: p_manufacturer_id OUT NOCOPY po_requisition_lines_All.MANUFACTURER_ID%TYPE
6612: )
6605: PROCEDURE get_item_attributes_values
6606: (
6607: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
6608: p_manufacturer_part_num OUT NOCOPY PO_ATTRIBUTE_VALUES.manufacturer_part_num%TYPE,
6609: p_manufacturer OUT NOCOPY PO_ATTRIBUTE_VALUES_TLP.manufacturer%TYPE,
6610: p_lead_time OUT NOCOPY PO_ATTRIBUTE_VALUES.lead_time%TYPE,
6611: p_manufacturer_id OUT NOCOPY po_requisition_lines_All.MANUFACTURER_ID%TYPE
6612: )
6613: IS
6606: (
6607: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
6608: p_manufacturer_part_num OUT NOCOPY PO_ATTRIBUTE_VALUES.manufacturer_part_num%TYPE,
6609: p_manufacturer OUT NOCOPY PO_ATTRIBUTE_VALUES_TLP.manufacturer%TYPE,
6610: p_lead_time OUT NOCOPY PO_ATTRIBUTE_VALUES.lead_time%TYPE,
6611: p_manufacturer_id OUT NOCOPY po_requisition_lines_All.MANUFACTURER_ID%TYPE
6612: )
6613: IS
6614: d_mod CONSTANT VARCHAR2(100) := D_get_item_attributes_values;
6694: -- None
6695: --Locks:
6696: -- None.
6697: --Function:
6698: -- To derive tlp item attributes to be populated in PO_ATTRIBUTE_VALUES_TLP.
6699: --
6700: --Parameters:
6701: --IN:
6702: -- p_inventory_item_id
6707: --------------------------------------------------------------------------------
6708: PROCEDURE get_item_attributes_tlp_values
6709: (
6710: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
6711: p_lang IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
6712: p_long_description OUT NOCOPY PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE
6713: )
6714: IS
6715: d_mod CONSTANT VARCHAR2(100) := D_get_item_attributes_tlp;
6708: PROCEDURE get_item_attributes_tlp_values
6709: (
6710: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
6711: p_lang IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
6712: p_long_description OUT NOCOPY PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE
6713: )
6714: IS
6715: d_mod CONSTANT VARCHAR2(100) := D_get_item_attributes_tlp;
6716: l_progress VARCHAR2(4);