[Home] [Help]
162: | NOTES VSANJAY Created 08/07/2003
163: | MODIFIED (MM/DD/YY)
164: | VSANJAY 08/07/2003
165: | AMRITUNJ 09/29/2003 - API Change and added commit after fnd_request.submit_request
166: | As specified in AOL standards guide for concurrent request API
167: | It can have side effects. For more info, search for COMMIT_NOTE
168: | in this file.
169: *=======================================================================*/
170:
220:
221: --if the po has T's and C's then launch the concurrent request to generate the pdf with T's and C's
222: --Bug5080617 Pass the parameters P_PO_TEMPLATE_CODE and P_CONTRACT_TEMPLATE_CODE as null
223:
224: IF l_document_subtype in ('STANDARD','BLANKET','CONTRACT') THEN
225: IF (l_with_terms = 'Y') THEN
226:
227: --
228: po_moac_utils_pvt.set_request_context(po_moac_utils_pvt.get_current_org_id);
343: END IF;
344:
345:
346: IF PO_COMMUNICATION_PROFILE = 'T' THEN
347: IF l_document_type in ('PO','PA') and l_document_subtype in ('STANDARD','BLANKET','CONTRACT')
348: or (l_document_type = 'RELEASE' and l_document_subtype = 'BLANKET' ) THEN
349: resultout := wf_engine.eng_completed || ':' || 'Y';
350: ELSE
351: resultout := wf_engine.eng_completed || ':' || 'N';
413: l_document_subtype := PO_WF_UTIL_PKG.GetItemAttrText (itemtype =>itemtype,
414: itemkey =>itemkey,
415: aname =>'DOCUMENT_SUBTYPE');
416:
417: IF l_document_type in ('PO','PA') and l_document_subtype in ( 'STANDARD','BLANKET','CONTRACT') THEN
418: l_entity_name := 'PO_HEAD';
419: ELSIF l_document_type = 'RELEASE' and l_document_subtype = 'BLANKET' THEN
420: l_entity_name :='PO_REL';
421: END IF;
747:
748:
749: select DECODE(p_document_subtype,'BLANKET',FND_MESSAGE.GET_STRING('POS','POS_POTYPE_BLKT'),
750: 'CONTRACT',FND_MESSAGE.GET_STRING('POS','POS_POTYPE_CNTR'),
751: 'STANDARD',FND_MESSAGE.GET_STRING('POS','POS_POTYPE_STD'),
752: 'PLANNED',FND_MESSAGE.GET_STRING('POS','POS_POTYPE_PLND')) into l_doc_display_name from dual;
753: if l_ga_flag = 'Y' then
754: l_doc_display_name := FND_MESSAGE.GET_STRING('PO','PO_GA_TYPE');
755: end if;
767: avalue => l_doc_num_rel);
768: end if;
769:
770: IF (p_document_type = 'PA' AND p_document_subtype IN ('BLANKET','CONTRACT')) OR
771: (p_document_type = 'PO' AND p_document_subtype = 'STANDARD') THEN
772:
773: l_doc_display_name := PO_DOC_STYLE_PVT.GET_STYLE_DISPLAY_NAME(l_document_id);
774:
775: END IF;
985: itemkey => itemkey,
986: aname => 'AUTHORIZATION_STATUS');
987:
988: IF l_document_type in ('PO','PA') and
989: l_document_subtype in ('STANDARD','BLANKET','CONTRACT') and
990: l_with_terms = 'Y' THEN
991:
992: l_header_id := l_document_id;
993:
1166: ) and ad.entity_name='PO_LINES')
1167: OR
1168: (ad.pk1_value in (select line_location_id from po_line_locations_all
1169: where po_header_id=l_po_header_id
1170: and shipment_type in ('PRICE BREAK','STANDARD', 'PREPAYMENT') --
1172: OR
1173: (ad.pk2_value in (select item_id from po_lines_all
1174: where po_header_id=l_po_header_id
1291: From those different calls, these procedures return the CP request IDs based on different
1292: priorities. Corrected the calls order and Request ID updation criteria based on their priorities.
1293: */
1294: /* Bug6841986/6528046
1295: IF l_document_type in ('PO','PA') and l_document_subtype in ('STANDARD','BLANKET','CONTRACT') THEN
1296: IF l_old_request_id is null and l_withterms = 'Y' THEN
1297: l_withterms := 'Y' ;
1298: ELSIF l_old_request_id is not null THEN
1299: l_withterms := 'N';
1466: ) and ad.entity_name='PO_LINES')
1467: OR
1468: (ad.pk1_value in (select line_location_id from po_line_locations_all
1469: where po_header_id=l_document_id
1470: and shipment_type in ('PRICE BREAK','STANDARD','PREPAYMENT') --
1472: OR
1473: (ad.pk2_value in (select item_id from po_lines_all
1474: where po_header_id=l_document_id
1693: */
1694: --End Bug6841986/6528046
1695:
1696:
1697: IF l_document_type in ('PO','PA') and l_document_subtype in ('STANDARD','BLANKET','CONTRACT') THEN
1698: l_header_id := l_document_id;
1699: ELSE
1700: SELECT po_header_id into l_header_id FROM po_releases_all
1701: WHERE po_release_id = l_document_id;
1987: END IF;
1988:
1989: if p_mode = 'PRINT' then
1990:
1991: if p_document_type in ('STANDARD','BLANKET','CONTRACT') then
1992:
1993: --
1994: po_moac_utils_pvt.set_request_context(po_moac_utils_pvt.get_current_org_id);
1995: --
2098:
2099:
2100: if p_mode = 'FAX' then
2101:
2102: if p_document_type in ('STANDARD','BLANKET','CONTRACT') then
2103:
2104: --
2105: po_moac_utils_pvt.set_request_context(po_moac_utils_pvt.get_current_org_id);
2106: --
2303: if p_document_type in ('BLANKET','CONTRACT') then
2304: l_doctype := 'PA';
2305: end if;
2306:
2307: if p_document_type = 'STANDARD' then
2308: l_doctype :='PO';
2309: end if;
2310:
2311: l_tc_buyer_gen_flag := 'N';
2316: l_store_flag := 'N';
2317:
2318: l_progress := '010';
2319: begin
2320: if p_document_type in ('STANDARD','BLANKET','CONTRACT') then
2321: l_entity_name := 'PO_HEAD';
2322: select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph
2323: where po_header_id = p_document_id and ph.vendor_site_id = pvs.vendor_site_id ;
2324: else
2477: else -- Authorization status is not in (Approved or Pre-Approved)
2478:
2479: l_progress := '060';
2480: Begin
2481: IF p_document_type in ('STANDARD','BLANKET','CONTRACT') THEN
2482: select max(revision_num)
2483: into l_revision_num
2484: from po_headers_archive_all
2485: where po_header_id = p_document_id
2640: l_store_flag,
2641: p_request_id);
2642:
2643: elsif p_mode = 'EMAIL' then
2644: if p_document_type in ('STANDARD','BLANKET','CONTRACT') then
2645: l_progress := '140';
2646: Start_Email_WF_Process( p_document_id,
2647: l_revision_num,
2648: l_doctype,
2717: commit;
2718: end if;
2719:
2720: l_progress := '200';
2721: if p_document_type in ('STANDARD','BLANKET','CONTRACT') then
2722: select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph
2723: where po_header_id = p_document_id and ph.vendor_site_id = pvs.vendor_site_id ;
2724: else
2725: select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph,po_releases_all pr
3835: -- None.
3836: --Function:
3837: -- Returns the value of workflow attribute ZIP_ERROR_CODE
3838: --Parameters:
3839: -- Follows standard workflow API specification
3840: --IN:
3841: --itemtype
3842: -- String that identifies the workflow process
3843: --itemkey
4049: ) and ad.entity_name='PO_LINES')
4050: OR
4051: (ad.pk1_value in (select line_location_id from po_line_locations_all
4052: where po_header_id=p_document_id
4053: and shipment_type in ('PRICE BREAK','STANDARD','PREPAYMENT') --
4055: OR
4056: (ad.pk2_value in (select item_id from po_lines_all
4057: where po_header_id=p_document_id
4994: SELECT count(*) into l_count FROM po_line_locations_all WHERE po_header_id = p_document_id;
4995:
4996: IF l_count >0 THEN
4997:
4998: /* Bug#3574748: Added the condition SHIPMENT_TYPE in ('BLANKET','STANDARD') in shipment query. */
4999: --Bug 5506417: Added order by pllx.shipment_num and order by plx.line_num clauses
5000: 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,
5001: PO_COMMUNICATION_PVT.getTimezone() timezone,PO_COMMUNICATION_PVT.getAmendmentMessage() ammendment_message,PO_COMMUNICATION_PVT.getTestFlag() test_flag,
5002: PO_COMMUNICATION_PVT.getDistinctShipmentCount() DIST_SHIPMENT_COUNT,
5007: CURSOR(SELECT plx.*, '||l_adv_amount_query||' CURSOR(SELECT PRICE_TYPE_DSP PRICE_TYPE, MULTIPLIER, MIN_MULTIPLIER,MAX_MULTIPLIER FROM po_price_differentials_v
5008: WHERE entity_type='''||l_eventType||''' AND entity_id = plx.po_line_id and enabled_flag=''Y'') AS price_diff,
5009: '|| l_line_short_attachment_query ||','||l_line_url_attachment_query||','||l_line_file_attachment_query||',
5010: CURSOR(SELECT pllx.*,';
5011: IF (p_document_subtype <> 'STANDARD') THEN
5012: l_xml_query := l_xml_query||'CURSOR(SELECT PRICE_TYPE_DSP PRICE_TYPE, MIN_MULTIPLIER, MAX_MULTIPLIER FROM po_price_differentials_v
5013: WHERE entity_type=''PRICE BREAK'' AND entity_id = pllx.line_location_id and enabled_flag=''Y'') AS price_break,';
5014: END IF;
5015:
5013: WHERE entity_type=''PRICE BREAK'' AND entity_id = pllx.line_location_id and enabled_flag=''Y'') AS price_break,';
5014: END IF;
5015:
5016: l_xml_query := l_xml_query || l_shipment_short_attach_query ||','||l_shipment_url_attach_query||','||l_shipment_file_attach_query||'
5017: 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
5018: (select ''x'' from po_line_locations_archive_all pllaa where pllaa.line_location_id = pllx.line_location_id
5019: 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 || '
5020: FROM po_lines_xml plx
5021: WHERE plx.po_header_id = phx.po_header_id and not exists
5061: END IF;
5062:
5063: ELSIF p_which_tables = 'ARCHIVE' THEN
5064:
5065: /* Bug#3574748: Added the condition SHIPMENT_TYPE in ('BLANKET','STANDARD') in shipment query. */
5066: /* Bug#3698674: SQL for generation of XML is framed by checking whether the values are exists at each
5067: level i.e line level, shipment level and distribution level. If the sql query is not framed with out
5068: checking the values exists in the corresponding levels in 8i "Exhausted Result" error is raised.
5069: */
5119: CURSOR(SELECT plx.*, '||l_adv_amount_query||' CURSOR(SELECT PRICE_TYPE_DSP PRICE_TYPE, MULTIPLIER, MIN_MULTIPLIER,MAX_MULTIPLIER FROM po_price_differentials_v
5120: WHERE entity_type='''||l_eventType||''' AND entity_id = plx.po_line_id and enabled_flag=''Y'') AS price_diff,
5121: '|| l_line_short_attachment_query ||','||l_line_url_attachment_query||','||l_line_file_attachment_query||',
5122: CURSOR(SELECT pllx.*,';
5123: IF (p_document_subtype <> 'STANDARD') THEN
5124: l_xml_query := l_xml_query||'CURSOR(SELECT PRICE_TYPE_DSP PRICE_TYPE, MIN_MULTIPLIER, MAX_MULTIPLIER FROM po_price_differentials_v
5125: WHERE entity_type=''PRICE BREAK'' AND entity_id = pllx.line_location_id and enabled_flag=''Y'') AS price_break,';
5126: END IF;
5127:
5125: WHERE entity_type=''PRICE BREAK'' AND entity_id = pllx.line_location_id and enabled_flag=''Y'') AS price_break,';
5126: END IF;
5127:
5128: l_xml_query := l_xml_query || l_shipment_short_attach_query ||','||l_shipment_url_attach_query||','||l_shipment_file_attach_query||'
5129: 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'')
5130: and pllx.revision_num = (SELECT MAX(plla.REVISION_NUM) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL plla
5131: where plla.LINE_LOCATION_ID = pllx.LINE_LOCATION_ID and plla.revision_num <= pcgt.revision_number )
5132: 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 || '
5133: FROM PO_LINES_ARCHIVE_XML plx WHERE plx.po_header_id = phx.po_header_id
5154: CURSOR(SELECT pllx.*, '|| l_shipment_short_attach_query ||','||l_shipment_url_attach_query||','||l_shipment_file_attach_query||',
5155: 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
5156: and pdx.REVISION_NUM = (SELECT MAX(pda.REVISION_NUM) FROM PO_DISTRIBUTIONS_ARCHIVE_ALL pda
5157: WHERE pda.PO_DISTRIBUTION_ID = pdx.PO_DISTRIBUTION_ID AND pda.REVISION_NUM <= pcgt.revision_number ) ) AS distributions
5158: 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'')
5159: and pllx.revision_num = (SELECT MAX(plla.REVISION_NUM) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL plla
5160: where plla.LINE_LOCATION_ID = pllx.LINE_LOCATION_ID and plla.revision_num <= pcgt.revision_number )
5161: 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 || '
5162: FROM PO_LINES_ARCHIVE_XML plx WHERE plx.po_header_id = phx.po_header_id
6773: return PO_COMMUNICATION_PVT.g_dist_shipto_count;
6774: end;
6775:
6776: /*
6777: Function to retrieve cancel date for Standard, Blanket and Contract PO's
6778: */
6779:
6780: function getPOCancelDate(p_po_header_id in NUMBER) return date is
6781: l_cancel_date date := null;
6784: INTO l_cancel_date
6785: FROM po_action_history pah
6786: WHERE pah.object_id = p_po_header_id
6787: AND ((pah.object_type_code = 'PO'
6788: AND pah.object_sub_type_code in ('PLANNED','STANDARD'))
6789: OR (pah.object_type_code = 'PA'
6790: AND pah.object_sub_type_code in ('BLANKET','CONTRACT')))
6791: AND pah.action_code = 'CANCEL';
6792: