[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 'TLP row for this language already exists'
3385: FROM PO_ATTRIBUTE_VALUES_TLP TLP2
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 'TLP row for this language already exists'
3385: FROM PO_ATTRIBUTE_VALUES_TLP TLP2
3386: WHERE TLP2.po_line_id = TLP1.po_line_id
3387: AND TLP2.req_template_name = TLP1.req_template_name
3388: AND TLP2.req_template_line_num = TLP1.req_template_line_num
3389: AND TLP2.org_id = TLP1.org_id
3433: p_req_template_line_num IN PO_REQEXPRESS_LINES_ALL.sequence_num%TYPE,
3434: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
3435: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
3436: p_org_id IN PO_LINES_ALL.org_id%TYPE,
3437: p_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE,
3438: -- Bug 7039409: Added new param p_manufacturer
3439: p_manufacturer IN PO_ATTRIBUTE_VALUES_TLP.manufacturer%TYPE
3440: )
3441: IS
3435: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
3436: p_org_id IN PO_LINES_ALL.org_id%TYPE,
3437: p_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE,
3438: -- Bug 7039409: Added new param p_manufacturer
3439: p_manufacturer IN PO_ATTRIBUTE_VALUES_TLP.manufacturer%TYPE
3440: )
3441: IS
3442: d_mod CONSTANT VARCHAR2(100) := D_create_default_attr_tlp;
3443: l_progress VARCHAR2(4);
3442: d_mod CONSTANT VARCHAR2(100) := D_create_default_attr_tlp;
3443: l_progress VARCHAR2(4);
3444:
3445: l_default_lang PO_HEADERS_ALL.created_language%TYPE;
3446: l_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE;
3447: l_long_description PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE;
3448: BEGIN
3449: l_progress := '010';
3450:
3443: l_progress VARCHAR2(4);
3444:
3445: l_default_lang PO_HEADERS_ALL.created_language%TYPE;
3446: l_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE;
3447: l_long_description PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE;
3448: BEGIN
3449: l_progress := '010';
3450:
3451: IF PO_LOG.d_proc THEN
3490: -- SQL What: Insert default rows for Attribute values TLP.
3491: -- This SQL will insert multiple rows, one for each installed lang.
3492: -- SQL Why : To create a default Attr TLP row
3493: -- SQL Join: po_line_id
3494: INSERT INTO PO_ATTRIBUTE_VALUES_TLP (
3495: attribute_values_tlp_id,
3496: po_line_id,
3497: req_template_name,
3498: req_template_line_num,
3515: program_update_date,
3516: last_updated_program
3517: )
3518: SELECT
3519: PO_ATTRIBUTE_VALUES_TLP_S.nextval,
3520: NVL(p_po_line_id,-2),
3521: NVL(p_req_template_name,'-2'),
3522: NVL(p_req_template_line_num,-2),
3523: NVL(p_ip_category_id,-2),
3540: d_mod -- last_updated_program
3541: FROM DUAL
3542: WHERE NOT EXISTS
3543: (SELECT 'TLP row for this language already exists'
3544: FROM PO_ATTRIBUTE_VALUES_TLP TLP2
3545: WHERE TLP2.po_line_id = p_po_line_id
3546: AND TLP2.req_template_name = p_req_template_name
3547: AND TLP2.req_template_line_num = p_req_template_line_num
3548: AND TLP2.org_id = p_org_id
3599: p_req_template_line_num IN PO_REQEXPRESS_LINES_ALL.sequence_num%TYPE,
3600: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
3601: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
3602: p_org_id IN PO_LINES_ALL.org_id%TYPE,
3603: p_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE
3604: )
3605: IS
3606: d_mod CONSTANT VARCHAR2(100) := D_create_default_attributes;
3607: l_progress VARCHAR2(4);
3608:
3609: l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
3610: -- Bug 7039409: Declared new variables
3611: l_manufacturer_part_num PO_ATTRIBUTE_VALUES.manufacturer_part_num%TYPE;
3612: l_manufacturer PO_ATTRIBUTE_VALUES_TLP.manufacturer%TYPE;
3613: l_lead_time PO_ATTRIBUTE_VALUES.lead_time%TYPE;
3614: BEGIN
3615: l_progress := '010';
3616:
3741: (
3742: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
3743: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
3744: p_org_id IN PO_LINES_ALL.org_id%TYPE,
3745: p_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE,
3746: p_organization_id IN NUMBER,
3747: p_master_organization_id IN NUMBER
3748: )
3749: IS
3871: PROCEDURE wipeout_category_attributes_MI
3872: (
3873: p_org_id IN PO_LINES_ALL.org_id%TYPE,
3874: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
3875: p_inventory_item_id IN PO_ATTRIBUTE_VALUES_TLP.inventory_item_id%TYPE,
3876: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
3877: p_item_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE
3878: )
3879: IS
3872: (
3873: p_org_id IN PO_LINES_ALL.org_id%TYPE,
3874: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
3875: p_inventory_item_id IN PO_ATTRIBUTE_VALUES_TLP.inventory_item_id%TYPE,
3876: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
3877: p_item_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE
3878: )
3879: IS
3880: d_mod CONSTANT VARCHAR2(100) := D_wipeout_category_attributes||'_MI';
3873: p_org_id IN PO_LINES_ALL.org_id%TYPE,
3874: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
3875: p_inventory_item_id IN PO_ATTRIBUTE_VALUES_TLP.inventory_item_id%TYPE,
3876: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
3877: p_item_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE
3878: )
3879: IS
3880: d_mod CONSTANT VARCHAR2(100) := D_wipeout_category_attributes||'_MI';
3881: l_progress VARCHAR2(4);
4018: l_progress := '030';
4019: -- SQL What: Wipeout category based attributes from TLP table for all Langs
4020: -- SQL Why : Because the category was changed
4021: -- SQL Join: po_line_id, req_template_name, req_template_line_num, org_id
4022: UPDATE PO_ATTRIBUTE_VALUES_TLP
4023: SET
4024: TL_TEXT_CAT_ATTRIBUTE1 = NULL,
4025: TL_TEXT_CAT_ATTRIBUTE2 = NULL,
4026: TL_TEXT_CAT_ATTRIBUTE3 = NULL,
4102: PROCEDURE update_attributes_MI
4103: (
4104: p_org_id IN PO_LINES_ALL.org_id%TYPE,
4105: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
4106: p_inventory_item_id IN PO_ATTRIBUTE_VALUES_TLP.inventory_item_id%TYPE,
4107: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
4108: p_item_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE,
4109: p_long_description IN PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE,
4110: p_organization_id IN NUMBER,
4103: (
4104: p_org_id IN PO_LINES_ALL.org_id%TYPE,
4105: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
4106: p_inventory_item_id IN PO_ATTRIBUTE_VALUES_TLP.inventory_item_id%TYPE,
4107: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
4108: p_item_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE,
4109: p_long_description IN PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE,
4110: p_organization_id IN NUMBER,
4111: p_master_organization_id IN NUMBER
4104: p_org_id IN PO_LINES_ALL.org_id%TYPE,
4105: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
4106: p_inventory_item_id IN PO_ATTRIBUTE_VALUES_TLP.inventory_item_id%TYPE,
4107: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
4108: p_item_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE,
4109: p_long_description IN PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE,
4110: p_organization_id IN NUMBER,
4111: p_master_organization_id IN NUMBER
4112: )
4105: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
4106: p_inventory_item_id IN PO_ATTRIBUTE_VALUES_TLP.inventory_item_id%TYPE,
4107: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
4108: p_item_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE,
4109: p_long_description IN PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE,
4110: p_organization_id IN NUMBER,
4111: p_master_organization_id IN NUMBER
4112: )
4113: IS
4115: l_progress VARCHAR2(4);
4116:
4117: l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
4118: l_orig_ip_category_id PO_LINES_ALL.ip_category_id%TYPE;
4119: l_orig_item_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE;
4120: l_manufacturer_part_num PO_ATTRIBUTE_VALUES.MANUFACTURER_PART_NUM%TYPE;
4121: l_orig_long_desc PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE;
4122: l_new_long_description PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE := nvl(p_long_description,' ');
4123: l_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
4117: l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
4118: l_orig_ip_category_id PO_LINES_ALL.ip_category_id%TYPE;
4119: l_orig_item_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE;
4120: l_manufacturer_part_num PO_ATTRIBUTE_VALUES.MANUFACTURER_PART_NUM%TYPE;
4121: l_orig_long_desc PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE;
4122: l_new_long_description PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE := nvl(p_long_description,' ');
4123: l_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
4124: l_proc_lead_time PO_ATTRIBUTE_VALUES.LEAD_TIME%TYPE;
4125: l_orig_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
4118: l_orig_ip_category_id PO_LINES_ALL.ip_category_id%TYPE;
4119: l_orig_item_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE;
4120: l_manufacturer_part_num PO_ATTRIBUTE_VALUES.MANUFACTURER_PART_NUM%TYPE;
4121: l_orig_long_desc PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE;
4122: l_new_long_description PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE := nvl(p_long_description,' ');
4123: l_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
4124: l_proc_lead_time PO_ATTRIBUTE_VALUES.LEAD_TIME%TYPE;
4125: l_orig_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
4126: l_inventory_org_id PO_LINES_ALL.org_id%TYPE;
4119: l_orig_item_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE;
4120: l_manufacturer_part_num PO_ATTRIBUTE_VALUES.MANUFACTURER_PART_NUM%TYPE;
4121: l_orig_long_desc PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE;
4122: l_new_long_description PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE := nvl(p_long_description,' ');
4123: l_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
4124: l_proc_lead_time PO_ATTRIBUTE_VALUES.LEAD_TIME%TYPE;
4125: l_orig_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
4126: l_inventory_org_id PO_LINES_ALL.org_id%TYPE;
4127: l_rec_tlp_for_lang VARCHAR2(100);
4121: l_orig_long_desc PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE;
4122: l_new_long_description PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE := nvl(p_long_description,' ');
4123: l_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
4124: l_proc_lead_time PO_ATTRIBUTE_VALUES.LEAD_TIME%TYPE;
4125: l_orig_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
4126: l_inventory_org_id PO_LINES_ALL.org_id%TYPE;
4127: l_rec_tlp_for_lang VARCHAR2(100);
4128:
4129: BEGIN
4261:
4262: BEGIN
4263: SELECT 'TLP row for this language already exists'
4264: INTO l_rec_tlp_for_lang
4265: FROM PO_ATTRIBUTE_VALUES_TLP TLP2
4266: WHERE TLP2.po_line_id = -2
4267: AND TLP2.req_template_name = '-2'
4268: AND TLP2.req_template_line_num = -2
4269: AND TLP2.org_id = p_org_id
4274: l_rec_tlp_for_lang:='';
4275: END;
4276:
4277: IF (l_rec_tlp_for_lang IS NULL OR l_rec_tlp_for_lang = '' ) THEN
4278: IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,l_progress,'no records hence insert into PO_ATTRIBUTE_VALUES_TLP ' ); END IF;
4279: l_progress := '090';
4280:
4281: INSERT INTO PO_ATTRIBUTE_VALUES_TLP TLP
4282: (
4277: IF (l_rec_tlp_for_lang IS NULL OR l_rec_tlp_for_lang = '' ) THEN
4278: IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,l_progress,'no records hence insert into PO_ATTRIBUTE_VALUES_TLP ' ); END IF;
4279: l_progress := '090';
4280:
4281: INSERT INTO PO_ATTRIBUTE_VALUES_TLP TLP
4282: (
4283: attribute_values_tlp_id,
4284: description,
4285: language,
4302: program_update_date,
4303: last_updated_program
4304: )
4305: SELECT
4306: PO_ATTRIBUTE_VALUES_TLP_S.nextval,
4307: p_item_description,
4308: p_language,
4309: -2,
4310: '-2',
4326: d_mod -- last_updated_program
4327: FROM dual;
4328:
4329: ELSE
4330: 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;
4331: l_progress := '095';
4332: SELECT description,Nvl(LONG_DESCRIPTION,''),Nvl(MANUFACTURER,'')
4333: INTO l_orig_item_description,l_orig_long_desc,l_orig_manufacturer
4334: FROM PO_ATTRIBUTE_VALUES_TLP
4330: 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;
4331: l_progress := '095';
4332: SELECT description,Nvl(LONG_DESCRIPTION,''),Nvl(MANUFACTURER,'')
4333: INTO l_orig_item_description,l_orig_long_desc,l_orig_manufacturer
4334: FROM PO_ATTRIBUTE_VALUES_TLP
4335: WHERE po_line_id = -2
4336: AND req_template_name = '-2'
4337: AND req_template_line_num = -2
4338: AND inventory_item_id = p_inventory_item_id
4353: -- SQL Join: po_line_id, req_template_name, req_template_line_num, org_id, language
4354:
4355:
4356:
4357: UPDATE PO_ATTRIBUTE_VALUES_TLP
4358: SET ip_category_id = p_ip_category_id,
4359: description = p_item_description,
4360: manufacturer = l_manufacturer,
4361: long_description = l_new_long_description,
4433: d_mod CONSTANT VARCHAR2(100) := D_gen_draft_line_translations;
4434: l_progress VARCHAR2(4);
4435:
4436: l_language_codes PO_TBL_NUMBER;
4437: l_tlp_row PO_ATTRIBUTE_VALUES_TLP%rowtype;
4438: l_po_line_id_list PO_TBL_NUMBER;
4439: l_tlp_id_list PO_TBL_NUMBER;
4440: BEGIN
4441: l_progress := '010';
4455: -- SQL Join: draft_id, po_header_id, po_line_id, language
4456: SELECT POLD.po_line_id, TLP.attribute_values_tlp_id
4457: BULK COLLECT INTO l_po_line_id_list, l_tlp_id_list
4458: FROM PO_LINES_DRAFT_ALL POLD,
4459: PO_ATTRIBUTE_VALUES_TLP TLP,
4460: PO_HEADERS_ALL POH
4461: WHERE POLD.draft_id = p_draft_id
4462: AND NVL(POLD.change_accepted_flag, 'Y') = 'Y'
4463: AND POLD.po_line_id = TLP.po_line_id
4656: l_progress := '030';
4657: -- SQL What: Wipeout category based attributes from TLP table for all Langs
4658: -- SQL Why : Because the category was changed
4659: -- SQL Join: po_line_id, req_template_name, req_template_line_num, org_id
4660: UPDATE PO_ATTRIBUTE_VALUES_TLP
4661: SET
4662: TL_TEXT_CAT_ATTRIBUTE1 = NULL,
4663: TL_TEXT_CAT_ATTRIBUTE2 = NULL,
4664: TL_TEXT_CAT_ATTRIBUTE3 = NULL,
4768: p_req_template_name IN PO_REQEXPRESS_LINES_ALL.express_name%TYPE,
4769: p_req_template_line_num IN PO_REQEXPRESS_LINES_ALL.sequence_num%TYPE,
4770: p_org_id IN PO_LINES_ALL.org_id%TYPE,
4771: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
4772: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
4773: p_item_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE
4774: )
4775: IS
4776: d_mod CONSTANT VARCHAR2(100) := D_update_attributes;
4769: p_req_template_line_num IN PO_REQEXPRESS_LINES_ALL.sequence_num%TYPE,
4770: p_org_id IN PO_LINES_ALL.org_id%TYPE,
4771: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
4772: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
4773: p_item_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE
4774: )
4775: IS
4776: d_mod CONSTANT VARCHAR2(100) := D_update_attributes;
4777: l_progress VARCHAR2(4);
4777: l_progress VARCHAR2(4);
4778:
4779: l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
4780: l_orig_ip_category_id PO_LINES_ALL.ip_category_id%TYPE;
4781: l_orig_item_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE;
4782: BEGIN
4783: l_progress := '010';
4784:
4785: IF PO_LOG.d_proc THEN
4880: -- have not changed.
4881: -- SQL Join: po_line_id, req_template_name, req_template_line_num, org_id
4882: SELECT description
4883: INTO l_orig_item_description
4884: FROM PO_ATTRIBUTE_VALUES_TLP
4885: WHERE po_line_id = NVL(p_po_line_id, -2)
4886: AND req_template_name = NVL(p_req_template_name, '-2')
4887: AND req_template_line_num = NVL(p_req_template_line_num, -2)
4888: AND org_id = p_org_id
4899: (p_item_description <> l_orig_item_description) ) THEN
4900: -- SQL What: Update Attribute TLP values
4901: -- SQL Why : To keep in synch with the Line level values
4902: -- SQL Join: po_line_id, req_template_name, req_template_line_num, org_id, language
4903: UPDATE PO_ATTRIBUTE_VALUES_TLP
4904: SET ip_category_id = p_ip_category_id,
4905: description = p_item_description,
4906: -- WHO columns
4907: LAST_UPDATE_LOGIN = FND_GLOBAL.login_id,
5647: -- SQL What: Insert new rows for Attribute values TLP.
5648: -- This SQL will enter multiple rows, one for each installed lang.
5649: -- SQL Why : To copy the TLP from old doc to new doc
5650: -- SQL Join: po_line_id
5651: INSERT INTO PO_ATTRIBUTE_VALUES_TLP (
5652: attribute_values_tlp_id,
5653: po_line_id,
5654: req_template_name,
5655: req_template_line_num,
5823: program_update_date,
5824: last_updated_program
5825: )
5826: SELECT
5827: PO_ATTRIBUTE_VALUES_TLP_S.nextval,
5828: p_new_po_line_id,
5829: POTLP.req_template_name,
5830: POTLP.req_template_line_num,
5831: POTLP.ip_category_id,
5996: POTLP.program_application_id,
5997: POTLP.program_id,
5998: POTLP.program_update_date,
5999: d_mod -- last_updated_program
6000: FROM PO_ATTRIBUTE_VALUES_TLP POTLP
6001: WHERE POTLP.po_line_id = p_orig_po_line_id
6002: AND NOT EXISTS
6003: (SELECT 'TLP row for this language already exists'
6004: FROM PO_ATTRIBUTE_VALUES_TLP TLP2
6000: FROM PO_ATTRIBUTE_VALUES_TLP POTLP
6001: WHERE POTLP.po_line_id = p_orig_po_line_id
6002: AND NOT EXISTS
6003: (SELECT 'TLP row for this language already exists'
6004: FROM PO_ATTRIBUTE_VALUES_TLP TLP2
6005: WHERE TLP2.po_line_id = p_new_po_line_id
6006: AND TLP2.req_template_name = POTLP.req_template_name
6007: AND TLP2.req_template_line_num = POTLP.req_template_line_num
6008: AND TLP2.org_id = POTLP.org_id
6140:
6141: -- SQL What: Delete Attribute Values TLP
6142: -- SQL Why : as required by this procedure
6143: -- SQL Join: po_line_id, req_template_name, req_template_line_num, org_id
6144: DELETE FROM PO_ATTRIBUTE_VALUES_TLP
6145: WHERE po_line_id = NVL(p_po_line_id, -2)
6146: AND req_template_name = NVL(p_req_template_name, '-2')
6147: AND req_template_line_num = NVL(p_req_template_line_num, -2)
6148: AND org_id = NVL(p_org_id, PO_MOAC_UTILS_PVT.get_current_org_id);
6213:
6214: -- SQL What: Delete Attribute Values TLP for all lines in a PO Header
6215: -- SQL Why : as required by this procedure
6216: -- SQL Join: po_line_id, po_header_id
6217: DELETE FROM PO_ATTRIBUTE_VALUES_TLP POTLP
6218: WHERE EXISTS
6219: (SELECT 'All PO Lines for the given Header'
6220: FROM PO_LINES_ALL POL
6221: WHERE POL.po_header_id = p_po_header_id
6287: (
6288: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
6289: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
6290: p_org_id IN PO_LINES_ALL.org_id%TYPE,
6291: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
6292: p_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE,
6293: p_long_description IN PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE,
6294: p_organization_id IN NUMBER,
6295: p_master_organization_id IN NUMBER
6288: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
6289: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
6290: p_org_id IN PO_LINES_ALL.org_id%TYPE,
6291: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
6292: p_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE,
6293: p_long_description IN PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE,
6294: p_organization_id IN NUMBER,
6295: p_master_organization_id IN NUMBER
6296: )
6289: p_ip_category_id IN PO_LINES_ALL.ip_category_id%TYPE,
6290: p_org_id IN PO_LINES_ALL.org_id%TYPE,
6291: p_language IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
6292: p_description IN PO_ATTRIBUTE_VALUES_TLP.description%TYPE,
6293: p_long_description IN PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE,
6294: p_organization_id IN NUMBER,
6295: p_master_organization_id IN NUMBER
6296: )
6297: IS
6296: )
6297: IS
6298: d_mod CONSTANT VARCHAR2(100) := D_create_attributes_tlp_MI;
6299: l_progress VARCHAR2(4);
6300: l_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE := nvl(p_description,' ');
6301: l_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
6302: l_long_description PO_ATTRIBUTE_VALUES_TLP.LONG_DESCRIPTION%TYPE := nvl(p_long_description,' ');
6303:
6304: BEGIN
6297: IS
6298: d_mod CONSTANT VARCHAR2(100) := D_create_attributes_tlp_MI;
6299: l_progress VARCHAR2(4);
6300: l_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE := nvl(p_description,' ');
6301: l_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
6302: l_long_description PO_ATTRIBUTE_VALUES_TLP.LONG_DESCRIPTION%TYPE := nvl(p_long_description,' ');
6303:
6304: BEGIN
6305: l_progress := '010';
6298: d_mod CONSTANT VARCHAR2(100) := D_create_attributes_tlp_MI;
6299: l_progress VARCHAR2(4);
6300: l_description PO_ATTRIBUTE_VALUES_TLP.description%TYPE := nvl(p_description,' ');
6301: l_manufacturer PO_ATTRIBUTE_VALUES_TLP.MANUFACTURER%TYPE;
6302: l_long_description PO_ATTRIBUTE_VALUES_TLP.LONG_DESCRIPTION%TYPE := nvl(p_long_description,' ');
6303:
6304: BEGIN
6305: l_progress := '010';
6306:
6318: END IF;
6319:
6320: l_progress := '030';
6321:
6322: -- list down the base descriptors in PO_ATTRIBUTE_VALUES_TLP
6323: -- MANUFACTURER COMMENTS ALIAS LONG_DESCRIPTION
6324: Begin
6325: SELECT MANUFACTURER_NAME
6326: INTO l_manufacturer
6340: -- SQL What: Insert default rows for Attribute values TLP.
6341: -- This SQL will insert multiple rows, one for each installed lang.
6342: -- SQL Why : To create a default Attr TLP row
6343: -- SQL Join: po_line_id
6344: INSERT INTO PO_ATTRIBUTE_VALUES_TLP (
6345: attribute_values_tlp_id,
6346: po_line_id,
6347: req_template_name,
6348: req_template_line_num,
6365: program_update_date,
6366: last_updated_program
6367: )
6368: SELECT
6369: PO_ATTRIBUTE_VALUES_TLP_S.nextval,
6370: -2,
6371: '-2',
6372: -2,
6373: NVL(p_ip_category_id,-2),
6390: d_mod -- last_updated_program
6391: FROM DUAL
6392: WHERE NOT EXISTS
6393: (SELECT 'TLP row for this language already exists'
6394: FROM PO_ATTRIBUTE_VALUES_TLP TLP2
6395: WHERE TLP2.inventory_item_id = p_inventory_item_id
6396: AND TLP2.org_id = p_org_id
6397: AND TLP2.language = p_language
6398: AND TLP2.po_line_id = -2
6435: PROCEDURE get_item_attributes_values
6436: (
6437: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
6438: p_manufacturer_part_num OUT NOCOPY PO_ATTRIBUTE_VALUES.manufacturer_part_num%TYPE,
6439: p_manufacturer OUT NOCOPY PO_ATTRIBUTE_VALUES_TLP.manufacturer%TYPE,
6440: p_lead_time OUT NOCOPY PO_ATTRIBUTE_VALUES.lead_time%TYPE
6441: )
6442: IS
6443: l_manufacturer_id po_requisition_lines_All.MANUFACTURER_ID%TYPE;
6472: PROCEDURE get_item_attributes_values
6473: (
6474: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
6475: p_manufacturer_part_num OUT NOCOPY PO_ATTRIBUTE_VALUES.manufacturer_part_num%TYPE,
6476: p_manufacturer OUT NOCOPY PO_ATTRIBUTE_VALUES_TLP.manufacturer%TYPE,
6477: p_lead_time OUT NOCOPY PO_ATTRIBUTE_VALUES.lead_time%TYPE,
6478: p_manufacturer_id OUT NOCOPY po_requisition_lines_All.MANUFACTURER_ID%TYPE
6479: )
6480: IS
6556: -- None
6557: --Locks:
6558: -- None.
6559: --Function:
6560: -- To derive tlp item attributes to be populated in PO_ATTRIBUTE_VALUES_TLP.
6561: --
6562: --Parameters:
6563: --IN:
6564: -- p_inventory_item_id
6569: --------------------------------------------------------------------------------
6570: PROCEDURE get_item_attributes_tlp_values
6571: (
6572: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
6573: p_lang IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
6574: p_long_description OUT NOCOPY PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE
6575: )
6576: IS
6577: d_mod CONSTANT VARCHAR2(100) := D_get_item_attributes_tlp;
6570: PROCEDURE get_item_attributes_tlp_values
6571: (
6572: p_inventory_item_id IN PO_LINES_ALL.item_id%TYPE,
6573: p_lang IN PO_ATTRIBUTE_VALUES_TLP.language%TYPE,
6574: p_long_description OUT NOCOPY PO_ATTRIBUTE_VALUES_TLP.long_description%TYPE
6575: )
6576: IS
6577: d_mod CONSTANT VARCHAR2(100) := D_get_item_attributes_tlp;
6578: l_progress VARCHAR2(4);