[Home] [Help]
167: | NOTES VSANJAY Created 08/07/2003
168: | MODIFIED (MM/DD/YY)
169: | VSANJAY 08/07/2003
170: | AMRITUNJ 09/29/2003 - API Change and added commit after fnd_request.submit_request
171: | As specified in AOL standards guide for concurrent request API
172: | It can have side effects. For more info, search for COMMIT_NOTE
173: | in this file.
174: *=======================================================================*/
175:
277:
278: --if the po has T's and C's then launch the concurrent request to generate the pdf with T's and C's
279: --Bug5080617 Pass the parameters P_PO_TEMPLATE_CODE and P_CONTRACT_TEMPLATE_CODE as null
280:
281: IF l_document_subtype in ('STANDARD', 'BLANKET', 'CONTRACT') THEN
282: IF (l_with_terms = 'Y') THEN
283:
284: /*Bug 8135201
285: Passing ICX_NUMERIC_CHARACTERS profile value to the POPDF program */
429: END IF;
430:
431:
432: IF PO_COMMUNICATION_PROFILE = 'T' THEN
433: IF l_document_type in ('PO', 'PA') and l_document_subtype in ('STANDARD', 'BLANKET', 'CONTRACT')
434: or (l_document_type = 'RELEASE' and l_document_subtype = 'BLANKET' ) THEN
435: resultout := wf_engine.eng_completed || ':' || 'Y';
436: ELSE
437: resultout := wf_engine.eng_completed || ':' || 'N';
514: ELSE
515: l_draft_id_char := null;
516: END IF;
517: --CLM
518: IF l_document_type in ('PO', 'PA') and l_document_subtype in ('STANDARD', 'BLANKET', 'CONTRACT') THEN
519: l_entity_name := 'PO_HEAD';
520: ELSIF l_document_type = 'RELEASE' and l_document_subtype = 'BLANKET' THEN
521: l_entity_name := 'PO_REL';
522: END IF;
856:
857:
858: select DECODE(p_document_subtype, 'BLANKET', FND_MESSAGE.GET_STRING('POS', 'POS_POTYPE_BLKT'),
859: 'CONTRACT', FND_MESSAGE.GET_STRING('POS', 'POS_POTYPE_CNTR'),
860: 'STANDARD', FND_MESSAGE.GET_STRING('POS', 'POS_POTYPE_STD'),
861: 'PLANNED', FND_MESSAGE.GET_STRING('POS', 'POS_POTYPE_PLND')) into l_doc_display_name from dual;
862: if l_ga_flag = 'Y' then
863: l_doc_display_name := FND_MESSAGE.GET_STRING('PO', 'PO_GA_TYPE');
864: end if;
876: avalue => l_doc_num_rel);
877: end if;
878:
879: IF (p_document_type = 'PA' AND p_document_subtype IN ('BLANKET', 'CONTRACT')) OR
880: (p_document_type = 'PO' AND p_document_subtype = 'STANDARD') THEN
881:
882: l_doc_display_name := PO_DOC_STYLE_PVT.GET_STYLE_DISPLAY_NAME(l_document_id);
883:
884: END IF;
1147: itemkey => itemkey,
1148: aname => 'AUTHORIZATION_STATUS');
1149:
1150: IF l_document_type in ('PO', 'PA') and
1151: l_document_subtype in ('STANDARD', 'BLANKET', 'CONTRACT') and
1152: l_with_terms = 'Y' THEN
1153:
1154: l_header_id := l_document_id;
1155:
1386: ) and ad.entity_name = 'PO_LINES')
1387: OR
1388: (ad.pk1_value in (select to_char(line_location_id) from po_line_locations_all
1389: where po_header_id = l_po_header_id
1390: and shipment_type in ('PRICE BREAK', 'STANDARD', 'PREPAYMENT') --
1392: OR
1393: (ad.pk2_value in (select item_id from po_lines_all
1394: where po_header_id = l_po_header_id
1537: priorities. Corrected the calls order and Request ID updation criteria based on their priorities.
1538: */
1539:
1540: /* Bug6841986/6528046
1541: IF l_document_type in ('PO','PA') and l_document_subtype in ('STANDARD','BLANKET','CONTRACT') THEN
1542: IF l_old_request_id is null and l_withterms = 'Y' THEN
1543: l_withterms := 'Y' ;
1544: ELSIF l_old_request_id is not null THEN
1545: l_withterms := 'N';
1712: ) and ad.entity_name = 'PO_LINES')
1713: OR
1714: (ad.pk1_value in (select to_char(line_location_id) from po_line_locations_all
1715: where po_header_id = l_document_id
1716: and shipment_type in ('PRICE BREAK', 'STANDARD', 'PREPAYMENT') --
1718: OR
1719: (ad.pk2_value in (select item_id from po_lines_all
1720: where po_header_id = l_document_id
1971: aname=>'REQUEST_ID');
1972: */
1973: --End Bug6841986/6528046
1974:
1975: IF l_document_type in ('PO', 'PA') and l_document_subtype in ('STANDARD', 'BLANKET', 'CONTRACT') THEN
1976: l_header_id := l_document_id;
1977: ELSE
1978: /* Bug 8372255 Added exception handler for the below sqls.*/
1979: BEGIN
2376: END IF;
2377:
2378: if p_mode = 'PRINT' then
2379:
2380: if p_document_type in ('STANDARD', 'BLANKET', 'CONTRACT') then
2381:
2382:
2383: --
2384: po_moac_utils_pvt.set_request_context(po_moac_utils_pvt.get_current_org_id);
2487:
2488:
2489: if p_mode = 'FAX' then
2490:
2491: if p_document_type in ('STANDARD', 'BLANKET', 'CONTRACT') then
2492:
2493: --
2494: po_moac_utils_pvt.set_request_context(po_moac_utils_pvt.get_current_org_id);
2495: --
2691: if p_document_type in ('BLANKET', 'CONTRACT') then
2692: l_doctype := 'PA';
2693: end if;
2694:
2695: if p_document_type = 'STANDARD' then
2696: l_doctype := 'PO';
2697: end if;
2698:
2699: l_tc_buyer_gen_flag := 'N';
2704: l_store_flag := 'N';
2705:
2706: l_progress := '010';
2707: begin
2708: if p_document_type in ('STANDARD', 'BLANKET', 'CONTRACT') then
2709: l_entity_name := 'PO_HEAD';
2710: select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph
2711: where po_header_id = p_document_id and ph.vendor_site_id = pvs.vendor_site_id ;
2712: else
2865: else -- Authorization status is not in (Approved or Pre-Approved)
2866:
2867: l_progress := '060';
2868: Begin
2869: IF p_document_type in ('STANDARD', 'BLANKET', 'CONTRACT') THEN
2870: select max(revision_num)
2871: into l_revision_num
2872: from po_headers_archive_all
2873: where po_header_id = p_document_id
3028: l_store_flag,
3029: p_request_id);
3030:
3031: elsif p_mode = 'EMAIL' then
3032: if p_document_type in ('STANDARD', 'BLANKET', 'CONTRACT') then
3033: l_progress := '140';
3034: Start_Email_WF_Process(p_document_id,
3035: l_revision_num,
3036: l_doctype,
3105: commit;
3106: end if;
3107:
3108: l_progress := '200';
3109: if p_document_type in ('STANDARD', 'BLANKET', 'CONTRACT') then
3110: select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph
3111: where po_header_id = p_document_id and ph.vendor_site_id = pvs.vendor_site_id ;
3112: else
3113: select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph, po_releases_all pr
4389: -- None.
4390: --Function:
4391: -- Returns the value of workflow attribute ZIP_ERROR_CODE
4392: --Parameters:
4393: -- Follows standard workflow API specification
4394: --IN:
4395: --itemtype
4396: -- String that identifies the workflow process
4397: --itemkey
4769: fnd_lobs fl
4770: where (
4771: (ad.pk1_value in (select line_location_id from po_line_locations_all
4772: where po_header_id=p_document_id
4773: and shipment_type in ('PRICE BREAK','STANDARD','PREPAYMENT') --
4775:
4776: and d.document_id = ad.document_id
4777: and dcu.category_id = d.category_id
5848: SELECT count(*) into l_count FROM po_line_locations_all WHERE po_header_id = p_document_id;
5849:
5850: IF l_count >0 THEN
5851:
5852: /* Bug#3574748: Added the condition SHIPMENT_TYPE in ('BLANKET','STANDARD') in shipment query. */
5853: --Bug 5506417: Added order by pllx.shipment_num and order by plx.line_num clauses
5854: l_xml_query := 'SELECT phx.*, PO_COMMUNICATION_PVT.getIsComplexWorkPO() is_complex_work_po, PO_COMMUNICATION_PVT.getDocumentType() document_type, PO_COMMUNICATION_PVT.getCoverMessage() cover_message,
5855: PO_COMMUNICATION_PVT.getTimezone() timezone,PO_COMMUNICATION_PVT.getAmendmentMessage() ammendment_message,PO_COMMUNICATION_PVT.getTestFlag() test_flag,
5856: PO_COMMUNICATION_PVT.getDistinctShipmentCount() DIST_SHIPMENT_COUNT,
5861: CURSOR(SELECT plx.*, '|| l_adv_amount_query ||' CURSOR(SELECT PRICE_TYPE_DSP PRICE_TYPE, MULTIPLIER, MIN_MULTIPLIER,MAX_MULTIPLIER FROM po_price_differentials_v
5862: WHERE entity_type='''|| l_eventType ||''' AND entity_id = plx.po_line_id and enabled_flag=''Y'') AS price_diff,
5863: '|| l_line_short_attachment_query || ',' || l_price_modifier_query1 || ',' || l_price_modifier_query2 || ',' || l_price_modifier_query3 || ',' || l_line_url_attachment_query || ',' || l_line_file_attachment_query ||',
5864: CURSOR(SELECT pllx.*,';
5865: IF (p_document_subtype <> 'STANDARD') THEN
5866: l_xml_query := l_xml_query ||'CURSOR(SELECT PRICE_TYPE_DSP PRICE_TYPE, MIN_MULTIPLIER, MAX_MULTIPLIER FROM po_price_differentials_v
5867: WHERE entity_type=''PRICE BREAK'' AND entity_id = pllx.line_location_id and enabled_flag=''Y'') AS price_break,';
5868: END IF;
5869: /*Bug 10388305 Added l_tax_name_query to get tax names*/
5867: WHERE entity_type=''PRICE BREAK'' AND entity_id = pllx.line_location_id and enabled_flag=''Y'') AS price_break,';
5868: END IF;
5869: /*Bug 10388305 Added l_tax_name_query to get tax names*/
5870: l_xml_query := l_xml_query || l_tax_name_query || ',' || l_shipment_short_attach_query || ',' || l_shipment_url_attach_query || ',' || l_shipment_file_attach_query ||'
5871: FROM po_line_locations_xml pllx WHERE pllx.po_line_id = plx.po_line_id and SHIPMENT_TYPE in (''BLANKET'',''STANDARD'',''PREPAYMENT'') AND NVL(pllx.payment_type,''NONE'') NOT IN (''ADVANCE'',''DELIVERY'') and not exists
5872: (select ''x'' from po_line_locations_archive_all pllaa where pllaa.line_location_id = pllx.line_location_id
5873: and pllaa.cancel_flag=''Y'' and pllaa.revision_num < PO_COMMUNICATION_PVT.getRevisionNum()) order by pllx.shipment_num ) AS line_locations' || l_complex_lloc_query || '
5874: FROM po_lines_xml plx
5875: WHERE plx.po_header_id = phx.po_header_id and not exists
5930: l_price_modifier_query3 := 'CURSOR (SELECT pha.segment1 ponum FROM po_headers_archive_all pha '
5931: || 'WHERE pha.po_header_id = plx.from_header_id) AS ADD_PRICE_PONUM';
5932: --
5933:
5934: /* Bug#3574748: Added the condition SHIPMENT_TYPE in ('BLANKET','STANDARD') in shipment query. */
5935: /* Bug#3698674: SQL for generation of XML is framed by checking whether the values are exists at each
5936: level i.e line level, shipment level and distribution level. If the sql query is not framed with out
5937: checking the values exists in the corresponding levels in 8i "Exhausted Result" error is raised.
5938: */
5989: CURSOR(SELECT plx.*, '|| l_adv_amount_query ||' CURSOR(SELECT PRICE_TYPE_DSP PRICE_TYPE, MULTIPLIER, MIN_MULTIPLIER,MAX_MULTIPLIER FROM po_price_differentials_v
5990: WHERE entity_type='''|| l_eventType ||''' AND entity_id = plx.po_line_id and enabled_flag=''Y'') AS price_diff,
5991: '|| l_line_short_attachment_query || ',' || l_price_modifier_query1 || ',' || l_price_modifier_query2 || ',' || l_price_modifier_query3 || ',' || l_line_url_attachment_query || ',' || l_line_file_attachment_query ||',
5992: CURSOR(SELECT pllx.*,';
5993: IF (p_document_subtype <> 'STANDARD') THEN
5994: l_xml_query := l_xml_query ||'CURSOR(SELECT PRICE_TYPE_DSP PRICE_TYPE, MIN_MULTIPLIER, MAX_MULTIPLIER FROM po_price_differentials_v
5995: WHERE entity_type=''PRICE BREAK'' AND entity_id = pllx.line_location_id and enabled_flag=''Y'') AS price_break,';
5996: END IF;
5997: /*Bug 10388305 Added l_tax_name_query to get tax names*/
5995: WHERE entity_type=''PRICE BREAK'' AND entity_id = pllx.line_location_id and enabled_flag=''Y'') AS price_break,';
5996: END IF;
5997: /*Bug 10388305 Added l_tax_name_query to get tax names*/
5998: l_xml_query := l_xml_query || l_tax_name_query || ',' || l_shipment_short_attach_query || ',' || l_shipment_url_attach_query || ',' || l_shipment_file_attach_query ||'
5999: FROM PO_LINE_LOCATIONS_ARCHIVE_XML pllx WHERE pllx.po_line_id = plx.po_line_id and SHIPMENT_TYPE in (''BLANKET'',''STANDARD'',''PREPAYMENT'') AND NVL(pllx.payment_type, ''NONE'') NOT IN (''ADVANCE'',''DELIVERY'')
6000: and pllx.revision_num = (SELECT /*+ push_subq no_unnest */ MAX(plla.REVISION_NUM) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL plla
6001: where plla.LINE_LOCATION_ID = pllx.LINE_LOCATION_ID and plla.revision_num <= pcgt.revision_number )
6002: and ''Y'' = decode(nvl(pllx.cancel_flag,''N''),''N'',''Y'',''Y'',decode(pllx.revision_num,PO_COMMUNICATION_PVT.getRevisionNum(),''Y'',''N'') ) order by pllx.shipment_num ) AS line_locations' || l_complex_lloc_query || '
6003: FROM PO_LINES_ARCHIVE_XML plx WHERE plx.po_header_id = phx.po_header_id
6025: CURSOR(SELECT pllx.*, ' || l_tax_name_query || ',' || l_shipment_short_attach_query || ',' || l_shipment_url_attach_query || ',' || l_shipment_file_attach_query ||',
6026: CURSOR(SELECT pdx.* FROM po_distribution_archive_xml pdx WHERE pdx.po_header_id = phx.po_header_id and pdx.LINE_LOCATION_ID = pllx.LINE_LOCATION_ID
6027: and pdx.REVISION_NUM = (SELECT MAX(pda.REVISION_NUM) FROM PO_DISTRIBUTIONS_ARCHIVE_ALL pda
6028: WHERE pda.PO_DISTRIBUTION_ID = pdx.PO_DISTRIBUTION_ID AND pda.REVISION_NUM <= pcgt.revision_number ) ) AS distributions
6029: FROM PO_LINE_LOCATIONS_ARCHIVE_XML pllx WHERE pllx.po_line_id = plx.po_line_id and SHIPMENT_TYPE in (''BLANKET'',''STANDARD'',''PREPAYMENT'') AND NVL(pllx.payment_type,''NONE'') NOT IN (''ADVANCE'',''DELIVERY'')
6030: and pllx.revision_num = (SELECT /*+ push_subq no_unnest */ MAX(plla.REVISION_NUM) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL plla
6031: where plla.LINE_LOCATION_ID = pllx.LINE_LOCATION_ID and plla.revision_num <= pcgt.revision_number )
6032: and ''Y'' = decode(nvl(pllx.cancel_flag,''N''),''N'',''Y'',''Y'',decode(pllx.revision_num,PO_COMMUNICATION_PVT.getRevisionNum(),''Y'',''N'') ) order by pllx.shipment_num ) AS line_locations'|| l_complex_dist_query || '
6033: FROM PO_LINES_ARCHIVE_XML plx WHERE plx.po_header_id = phx.po_header_id
6073: -- Added release id in condition
6074: --Bug 5506417: Added order by pllx.shipment_num and order by plx.line_num clauses
6075: /* Bug 8886244 .
6076: As Part of This Bug ,removed the variables l_price_modifier_query1,l_price_modifier_query2,l_price_modifier_query3 while constructing
6077: l_xml_query. This is because we are initializing these variables incase of Standard,Blanket Purchase Orders Only.
6078: These variables are null incase of RELEASE due to which PO Communication program was failing with missing expression error for Releases.
6079: As we did Pricing Enhancement in case of Standard,Blanket only we need not have these variables incase of Release while constructin the queries.
6080: */
6081: /*Bug 10388305 Added l_tax_name_query to get tax names*/
6075: /* Bug 8886244 .
6076: As Part of This Bug ,removed the variables l_price_modifier_query1,l_price_modifier_query2,l_price_modifier_query3 while constructing
6077: l_xml_query. This is because we are initializing these variables incase of Standard,Blanket Purchase Orders Only.
6078: These variables are null incase of RELEASE due to which PO Communication program was failing with missing expression error for Releases.
6079: As we did Pricing Enhancement in case of Standard,Blanket only we need not have these variables incase of Release while constructin the queries.
6080: */
6081: /*Bug 10388305 Added l_tax_name_query to get tax names*/
6082: l_xml_query := 'SELECT phx.*, PO_COMMUNICATION_PVT.getDocumentType() document_type, PO_COMMUNICATION_PVT.getCoverMessage() cover_message,
6083: PO_COMMUNICATION_PVT.getTimezone() timezone,PO_COMMUNICATION_PVT.getAmendmentMessage() ammendment_message,PO_COMMUNICATION_PVT.getTestFlag() test_flag,
6116: -- Bug 3727808. Use blanket revision number rather than release revision number. Added the max(pb.revision_num) query in lines SQL
6117: -- Bug 5506417: Added order by pllx.shipment_num and order by plx.line_num clauses
6118: /* Bug 8886244 .
6119: As Part of This Bug ,removed the variables l_price_modifier_query1,l_price_modifier_query2,l_price_modifier_query3 while constructing
6120: l_xml_query. This is because we are initializing these variables incase of Standard,Blanket Purchase Orders Only.
6121: These variables are null incase of RELEASE due to which PO Communication program was failing with missing expression error for Releases.
6122: As we did Pricing Enhancement in case of Standard,Blanket only we need not have these variables incase of Release while constructin the queries.
6123: */
6124: /*Bug 10388305 Added l_tax_name_query to get tax names*/
6118: /* Bug 8886244 .
6119: As Part of This Bug ,removed the variables l_price_modifier_query1,l_price_modifier_query2,l_price_modifier_query3 while constructing
6120: l_xml_query. This is because we are initializing these variables incase of Standard,Blanket Purchase Orders Only.
6121: These variables are null incase of RELEASE due to which PO Communication program was failing with missing expression error for Releases.
6122: As we did Pricing Enhancement in case of Standard,Blanket only we need not have these variables incase of Release while constructin the queries.
6123: */
6124: /*Bug 10388305 Added l_tax_name_query to get tax names*/
6125: l_xml_query := 'SELECT phx.*, PO_COMMUNICATION_PVT.getDocumentType() document_type, PO_COMMUNICATION_PVT.getCoverMessage() cover_message,PO_COMMUNICATION_PVT.getTimezone() timezone,
6126: PO_COMMUNICATION_PVT.getAmendmentMessage() ammendment_message,PO_COMMUNICATION_PVT.getTestFlag() test_flag,
7965: return PO_COMMUNICATION_PVT.g_dist_shipto_count;
7966: end;
7967:
7968: /*
7969: Function to retrieve cancel date for Standard, Blanket and Contract PO's
7970: */
7971:
7972: function getPOCancelDate(p_po_header_id in NUMBER) return date is
7973: l_cancel_date date := null;
7976: INTO l_cancel_date
7977: FROM po_action_history pah
7978: WHERE pah.object_id = p_po_header_id
7979: AND ((pah.object_type_code = 'PO'
7980: AND pah.object_sub_type_code in ('PLANNED', 'STANDARD'))
7981: OR (pah.object_type_code = 'PA'
7982: AND pah.object_sub_type_code in ('BLANKET', 'CONTRACT')))
7983: AND pah.action_code = 'CANCEL';
7984: