[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
on the columns that will be selected from the Materialized View. Also, the mapping of these
results to the AK Region Items is done in this function.
*/
FUNCTION get_status_sel_clause(p_view_by_dim in VARCHAR2
,p_view_by_col in VARCHAR2
,p_url in VARCHAR2) return VARCHAR2 IS
l_sel_clause varchar2(8000);
l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
,'PO'
,'8.0');
(select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
from (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
on the columns that will be selected from the Materialized View. Also, the mapping of these
results to the AK Region Items is done in this function.
*/
FUNCTION get_awd_status_sel_clause(p_view_by_dim in VARCHAR2
,p_view_by_col in VARCHAR2
) return VARCHAR2 IS
l_sel_clause varchar2(8000);
l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
,'NEG'
,'8.0');
(select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
from (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
on the columns that will be selected from the Materialized View. Also, the mapping of these
results to the AK Region Items is done in this function.
*/
FUNCTION get_avg_cycle_time_sel_clause(p_view_by_dim in VARCHAR2
,p_view_by_col in VARCHAR2
) return VARCHAR2 IS
l_sel_clause varchar2(8000);
l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
,'NEG'
,'8.0');
(select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
from (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
on the columns that will be selected from the Materialized View. Also, the mapping of these
results to the AK Region Items is done in this function.
*/
FUNCTION get_realized_status_sel_clause(p_view_by_dim in VARCHAR2
,p_view_by_col in VARCHAR2
) return VARCHAR2 IS
l_sel_clause varchar2(8000);
l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
,'PO'
,'8.0');
(select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
from (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
on the columns that will be selected from the Materialized View. Also, the mapping of these
results to the AK Region Items is done in this function.
*/
FUNCTION get_awd_trend_sel_clause return VARCHAR2
IS
l_sel_clause varchar2(4000);
l_sel_clause := 'select cal.name VIEWBY,';
on the columns that will be selected from the Materialized View. Also, the mapping of these
results to the AK Region Items is done in this function.
*/
FUNCTION get_avg_cycle_trend_sel_clause return VARCHAR2
IS
l_sel_clause varchar2(4000);
l_sel_clause := 'select cal.name VIEWBY,';
on the columns that will be selected from the Materialized View. Also, the mapping of these
results to the AK Region Items is done in this function.
*/
FUNCTION get_prj_svng_trend_sel_clause return VARCHAR2
IS
l_sel_clause varchar2(4000);
l_sel_clause := 'select cal.name VIEWBY,';
on the columns that will be selected from the Materialized View. Also, the mapping of these
results to the AK Region Items is done in this function.
*/
FUNCTION get_prj_ln_trend_sel_clause return VARCHAR2
IS
l_sel_clause varchar2(4000);
l_sel_clause := 'select cal.name VIEWBY,';
on the columns that will be selected from the Materialized View. Also, the mapping of these
results to the AK Region Items is done in this function.
*/
FUNCTION get_real_svng_trend_sel_clause return VARCHAR2
IS
l_sel_clause varchar2(4000);
l_sel_clause := 'select cal.name VIEWBY,';
on the columns that will be selected from the Materialized View. Also, the mapping of these
results to the AK Region Items is done in this function.
*/
FUNCTION get_neg_po_trend_sel_clause return VARCHAR2
IS
l_sel_clause varchar2(4000);
l_sel_clause := 'select cal.name VIEWBY,';
'SELECT
ponh.document_number POA_ATTRIBUTE2,
ponip.disp_line_number POA_ATTRIBUTE3,
item.value POA_ATTRIBUTE4,
negorg.name POA_ATTRIBUTE5,
doctl.name POA_ATTRIBUTE6,
hrv.value POA_ATTRIBUTE7,
supv.value POA_ATTRIBUTE8,
decode(i.contract_type, ''BLANKET'', ''Blanket Agreement'',''STANDARD'', ''Standard PO'',''CONTRACT'',''Contract Agreement'' ) POA_ATTRIBUTE9,
poh.segment1 POA_ATTRIBUTE10,
uom.description POA_ATTRIBUTE11,
i.POA_MEASURE1 POA_MEASURE1,
i.POA_MEASURE2 POA_MEASURE2,
i.POA_MEASURE3 POA_MEASURE3,
''pFunctionName=POA_DBI_NEG_DRILL&AuctionId=''||i.auction_header_id||''&addBreadCrumb=Y&retainAM=Y'' POA_ATTRIBUTE14,
decode(i.po_header_id, null, null,
decode(poh.authorization_status,''APPROVED'',
''pFunctionName=POA_DBI_ISP_DRILL&PoHeaderId=''||i.po_header_id||''&addBreadCrumb=Y&retainAM=Y'',
''pFunctionName=POA_DBI_PDF_DRILL&DocumentId='' || i.po_header_id || ''&RevisionNum=''
|| poh.revision_num || ''&LanguageCode='' || userenv(''LANG'') || ''&DocumentType=PO&DocumentSubtype=STANDARD&OrgId='' || poh.org_id
|| ''&UserSecurity=Y&StoreFlag=N&ViewOrCommunicate=View&CallFromForm=N''
)) POA_ATTRIBUTE15,
i.POA_ATTRIBUTE16 POA_ATTRIBUTE16,
i.POA_ATTRIBUTE17 POA_ATTRIBUTE17
FROM
(
SELECT
(rank() over(&ORDER_BY_CLAUSE nulls last, auction_header_id, auction_line_number,
bid_number, bid_line_number, org_id, po_item_id, base_uom)) - 1 rnk,
auction_header_id,
auction_line_number,
bid_number,
bid_line_number,
doctype_id,
po_item_id,
org_id,
negotiation_creator_id,
supplier_id,
POA_MEASURE1,
POA_MEASURE2,
POA_MEASURE3,
contract_type,
po_header_id,
base_uom,
POA_ATTRIBUTE16,
POA_ATTRIBUTE17
FROM
(SELECT
fact.auction_header_id,
fact.auction_header_id POA_ATTRIBUTE2,
fact.auction_line_number,
fact.bid_number,
fact.bid_line_number,
fact.doctype_id,
fact.po_item_id,
fact.org_id,
fact.negotiation_creator_id,
fact.supplier_id,
sum(fact.award_qty) POA_MEASURE1,
sum(fact.award_amount_' || l_cur_suffix || ') POA_MEASURE2, -- Add currency suffix
sum(sum(fact.award_amount_' || l_cur_suffix || ')) over() POA_MEASURE3,
fact.contract_type,
fact.po_header_id,
fact.base_uom,
fact.auction_creation_date POA_ATTRIBUTE16, -- Add creation date
nvl(fact.rfi_complete_date,fact.award_complete_date) POA_ATTRIBUTE17 -- Add completed date
from
poa_dbi_neg_f_v fact
where
trunc(nvl(fact.rfi_complete_date, fact.award_complete_date)) between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE
' || fnd_global.newline || l_filter_rfi || l_where_clause || '
group by
fact.auction_header_id,
fact.auction_line_number,
fact.bid_number,
fact.bid_line_number,
fact.doctype_id,
fact.po_item_id,
fact.org_id,
fact.negotiation_creator_id,
fact.supplier_id,
fact.contract_type,
fact.po_header_id,
fact.base_uom,
fact.auction_creation_date,
nvl(fact.rfi_complete_date,fact.award_complete_date)
)
where ' || get_awd_dtl_filter_clause || '
) i ,
pon_auction_headers_all ponh,
pon_auction_item_prices_all ponip,
pon_bid_headers ponbh,
pon_bid_item_prices ponbip,
poa_items_v item,
poa_suppliers_v supv,
hri_cl_per_v hrv,
mtl_units_of_measure_vl uom,
hr_all_organization_units_vl negorg,
pon_auc_doctypes_tl doctl,
po_headers_all poh
WHERE
i.auction_header_id = ponh.auction_header_id
and i.auction_line_number = ponip.line_number
and ponh.auction_header_id = ponip.auction_header_id
and decode(ponh.award_status, ''QUALIFIED'', null, ponh.auction_header_id) = ponbh.auction_header_id(+) /* Include only the Auction Record of RFI and not the Responses */
and ponbh.auction_header_id = ponbip.auction_header_id(+) /* For Bidded Transactions Only */
and ponbh.bid_number = ponbip.bid_number(+)
and nvl(ponbip.auction_line_number,ponip.line_number) = ponip.line_number /* Filter to give unique record */
AND nvl(ponbh.bid_number,nvl(i.bid_number,-99)) = nvl(i.bid_number,-99)
AND nvl(ponbip.line_number, nvl(i.bid_line_number,-99)) = nvl(i.bid_line_number,-99)
and nvl(ponbh.bid_status,''ACTIVE'') = ''ACTIVE'' /* If a Supplier changes bids, they store ARCHIVED. Ignore them. */
and nvl(ponbip.award_status,''-999'') <> ''REJECTED'' /* Cannot be NULL or REJECTED */
and nvl(ponh.award_status,''-999'') <> ''NO''
and ponh.doctype_id = doctl.doctype_id
and i.po_item_id = item.id
and i.supplier_id = supv.id(+)
and i.negotiation_creator_id = hrv.id
AND SYSDATE BETWEEN hrv.start_date AND hrv.end_date
and i.base_uom = uom.unit_of_measure(+)
and i.org_id = negorg.organization_id
AND i.po_header_id = poh.po_header_id(+)
AND i.doctype_id = doctl.doctype_id
AND doctl.LANGUAGE = USERENV(''LANG'')
AND (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
ORDER BY rnk ';
on the columns that will be selected from the Materialized View. Also, the mapping of these
results to the AK Region Items is done in this function.
*/
FUNCTION get_awd_dtl_filter_clause return VARCHAR2
IS
l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
on the columns that will be selected from the Materialized View. Also, the mapping of these
results to the AK Region Items is done in this function.
*/
FUNCTION get_dtl_filter(p_doctype_id IN VARCHAR2, show_rfi IN VARCHAR2) return VARCHAR2
IS
l_dtl_filter VARCHAR2(100);
l_selected_doctype VARCHAR2(10);
SELECT count(*) INTO l_selected_doctype FROM POA_NEG_DOCTYPES_V WHERE INTERNAL_NAME NOT IN ('REQUEST_FOR_INFORMATION')
AND ID = p_doctype_id;
IF(l_selected_doctype > 0) THEN
RETURN '1';