[Home] [Help]
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
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
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
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);
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:
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
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);
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;
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);
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
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
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;
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
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,
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,
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
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);
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
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;
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
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);