eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.DDR_WEBSERVICES_PUB dependencies on DDR_WEBSERVICES_CONSTANTS

Line 502: UPDATE DDR_WS_JOB set status= ddr_webservices_constants.g_ret_sts_running,start_date=sysdate where job_id=p_job_id ;

498: l_job_id NUMBER:=null;
499: BEGIN
500: l_job_id:=p_job_id;
501: --update job status to Running
502: UPDATE DDR_WS_JOB set status= ddr_webservices_constants.g_ret_sts_running,start_date=sysdate where job_id=p_job_id ;
503: --build dynamic query
504: get_dyn_query(p_api_version,
505: 'A',
506: p_mfg_org_cd,

Line 525: IF l_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

521: l_return_status,
522: l_msg_count,
523: l_msg_data,
524: l_query);
525: IF l_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
526: UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
527: RETURN;
528: END IF;
529: --write data to xml file based on dynamic query generated

Line 532: UPDATE DDR_WS_JOB SET status=ddr_webservices_constants.g_ret_sts_success,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;

528: END IF;
529: --write data to xml file based on dynamic query generated
530: write_fact_to_xml_file(l_query,p_fact_code,l_job_id,l_return_status,l_msg_count,l_msg_data);
531: --update job status to complete/error
532: UPDATE DDR_WS_JOB SET status=ddr_webservices_constants.g_ret_sts_success,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
533: EXCEPTION
534: WHEN NO_DATA_FOUND THEN
535: l_return_status:=ddr_webservices_constants.g_ret_sts_error;
536: l_msg_count:=1;

Line 535: l_return_status:=ddr_webservices_constants.g_ret_sts_error;

531: --update job status to complete/error
532: UPDATE DDR_WS_JOB SET status=ddr_webservices_constants.g_ret_sts_success,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
533: EXCEPTION
534: WHEN NO_DATA_FOUND THEN
535: l_return_status:=ddr_webservices_constants.g_ret_sts_error;
536: l_msg_count:=1;
537: l_msg_data:='No Data Found. Error code:'||sqlcode||' Error message:'||sqlerrm;
538: UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
539: WHEN OTHERS THEN

Line 540: l_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

536: l_msg_count:=1;
537: l_msg_data:='No Data Found. Error code:'||sqlcode||' Error message:'||sqlerrm;
538: UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
539: WHEN OTHERS THEN
540: l_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
541: l_msg_count:=1;
542: l_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
543: UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
544: END ddr_fact_aggr_prc;

Line 573: UPDATE DDR_WS_JOB set status= ddr_webservices_constants.g_ret_sts_running,start_date=sysdate where job_id=p_job_id ;

569: l_job_id NUMBER:=null;
570: BEGIN
571: l_job_id:=p_job_id;
572: --update job status to Running
573: UPDATE DDR_WS_JOB set status= ddr_webservices_constants.g_ret_sts_running,start_date=sysdate where job_id=p_job_id ;
574: --build dynamic query
575: get_dyn_query(p_api_version,
576: 'D',
577: p_mfg_org_cd,

Line 597: IF l_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

593: l_msg_count,
594: l_msg_data,
595: l_query);
596:
597: IF l_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
598: UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
599: RETURN;
600: END IF;
601: --write data to xml file based on dynamic query generated

Line 604: UPDATE DDR_WS_JOB SET status=ddr_webservices_constants.g_ret_sts_success,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;

600: END IF;
601: --write data to xml file based on dynamic query generated
602: write_fact_to_xml_file(l_query,p_fact_code,l_job_id,l_return_status,l_msg_count,l_msg_data);
603: --update job status to complete/error
604: UPDATE DDR_WS_JOB SET status=ddr_webservices_constants.g_ret_sts_success,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
605: EXCEPTION
606: WHEN NO_DATA_FOUND THEN
607: l_return_status:=ddr_webservices_constants.g_ret_sts_error;
608: l_msg_count:=1;

Line 607: l_return_status:=ddr_webservices_constants.g_ret_sts_error;

603: --update job status to complete/error
604: UPDATE DDR_WS_JOB SET status=ddr_webservices_constants.g_ret_sts_success,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
605: EXCEPTION
606: WHEN NO_DATA_FOUND THEN
607: l_return_status:=ddr_webservices_constants.g_ret_sts_error;
608: l_msg_count:=1;
609: l_msg_data:='No Data Found. Error code:'||sqlcode||' Error message:'||sqlerrm;
610: UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
611: WHEN OTHERS THEN

Line 612: l_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

608: l_msg_count:=1;
609: l_msg_data:='No Data Found. Error code:'||sqlcode||' Error message:'||sqlerrm;
610: UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
611: WHEN OTHERS THEN
612: l_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
613: l_msg_count:=1;
614: l_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
615: UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
616: END ddr_fact_details_prc;

Line 682: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

678: p_fact_code,
679: x_return_status,
680: x_msg_count,
681: x_msg_data);
682: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
683: RETURN;
684: END IF;
685: -- get fact table name
686: get_fact_table(p_fact_code,x_return_status,x_msg_count,x_msg_data,l_fact_table_name);

Line 706: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

702: x_msg_data,
703: l_itm_ref_tbls,
704: l_itm_ref_joins,
705: l_itm_where_clus);
706: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
707: RETURN;
708: END IF;
709: END IF;
710: --get the reference tables ,reference table joins and where clause join for the organization hierarchy level

Line 722: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

718: x_msg_data,
719: l_org_ref_tbls,
720: l_org_ref_joins,
721: l_org_where_clus);
722: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
723: RETURN;
724: END IF;
725: END IF;
726: --get the reference tables ,reference table joins and where clause join for the time hierarchy level

Line 739: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

735: x_msg_data,
736: l_time_ref_tbls,
737: l_time_ref_joins,
738: l_time_where_clus);
739: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
740: RETURN;
741: END IF;
742: END IF;
743: --get the reference tables ,reference table joins and where clause join for the location hierarchy level

Line 756: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

752: x_msg_data,
753: l_loc_ref_tbls,
754: l_loc_ref_joins,
755: l_loc_where_clus);
756: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
757: RETURN;
758: END IF;
759: END IF;
760: --Bug 6880404 change start

Line 778: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

774: x_return_status,
775: x_msg_count,
776: x_msg_data,
777: l_group_col);
778: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
779: RETURN;
780: END IF;
781: END IF;
782:

Line 837: x_return_status := ddr_webservices_constants.g_ret_sts_error;

833: -- DBMS_OUTPUT.PUT_LINE('l_query='||l_query);
834: x_dyn_query:= l_query;
835: EXCEPTION
836: WHEN NO_DATA_FOUND THEN
837: x_return_status := ddr_webservices_constants.g_ret_sts_error;
838: x_msg_count := 1;
839: x_msg_data := 'No Data Found. Error Code' ||sqlcode||' Error message:'||sqlerrm;
840: WHEN OTHERS THEN
841: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

Line 841: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

837: x_return_status := ddr_webservices_constants.g_ret_sts_error;
838: x_msg_count := 1;
839: x_msg_data := 'No Data Found. Error Code' ||sqlcode||' Error message:'||sqlerrm;
840: WHEN OTHERS THEN
841: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
842: x_msg_count:=1;
843: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
844: END get_dyn_query;
845:

Line 856: WHEN ddr_webservices_constants.g_misd_cd THEN

852: BEGIN
853: --case condition to determine name of the fact table
854: CASE p_fact_code
855: -- for MARKET ITEM SALES DAY
856: WHEN ddr_webservices_constants.g_misd_cd THEN
857: x_fact_name:= ddr_webservices_constants.g_misd_fact_tbl;
858: -- for PROMOTION PLAN
859: WHEN ddr_webservices_constants.g_pp_cd THEN
860: x_fact_name:= ddr_webservices_constants.g_pp_fact_tbl;

Line 857: x_fact_name:= ddr_webservices_constants.g_misd_fact_tbl;

853: --case condition to determine name of the fact table
854: CASE p_fact_code
855: -- for MARKET ITEM SALES DAY
856: WHEN ddr_webservices_constants.g_misd_cd THEN
857: x_fact_name:= ddr_webservices_constants.g_misd_fact_tbl;
858: -- for PROMOTION PLAN
859: WHEN ddr_webservices_constants.g_pp_cd THEN
860: x_fact_name:= ddr_webservices_constants.g_pp_fact_tbl;
861: -- for RETAIL INVENTORY ITEM DAY

Line 859: WHEN ddr_webservices_constants.g_pp_cd THEN

855: -- for MARKET ITEM SALES DAY
856: WHEN ddr_webservices_constants.g_misd_cd THEN
857: x_fact_name:= ddr_webservices_constants.g_misd_fact_tbl;
858: -- for PROMOTION PLAN
859: WHEN ddr_webservices_constants.g_pp_cd THEN
860: x_fact_name:= ddr_webservices_constants.g_pp_fact_tbl;
861: -- for RETAIL INVENTORY ITEM DAY
862: WHEN ddr_webservices_constants.g_riid_cd THEN
863: x_fact_name:= ddr_webservices_constants.g_riid_fact_tbl;

Line 860: x_fact_name:= ddr_webservices_constants.g_pp_fact_tbl;

856: WHEN ddr_webservices_constants.g_misd_cd THEN
857: x_fact_name:= ddr_webservices_constants.g_misd_fact_tbl;
858: -- for PROMOTION PLAN
859: WHEN ddr_webservices_constants.g_pp_cd THEN
860: x_fact_name:= ddr_webservices_constants.g_pp_fact_tbl;
861: -- for RETAIL INVENTORY ITEM DAY
862: WHEN ddr_webservices_constants.g_riid_cd THEN
863: x_fact_name:= ddr_webservices_constants.g_riid_fact_tbl;
864: -- for RETAIL SALE RETURN ITEM DAY fact

Line 862: WHEN ddr_webservices_constants.g_riid_cd THEN

858: -- for PROMOTION PLAN
859: WHEN ddr_webservices_constants.g_pp_cd THEN
860: x_fact_name:= ddr_webservices_constants.g_pp_fact_tbl;
861: -- for RETAIL INVENTORY ITEM DAY
862: WHEN ddr_webservices_constants.g_riid_cd THEN
863: x_fact_name:= ddr_webservices_constants.g_riid_fact_tbl;
864: -- for RETAIL SALE RETURN ITEM DAY fact
865: WHEN ddr_webservices_constants.g_rsrid_cd THEN
866: x_fact_name:= ddr_webservices_constants.g_rsrid_fact_tbl;

Line 863: x_fact_name:= ddr_webservices_constants.g_riid_fact_tbl;

859: WHEN ddr_webservices_constants.g_pp_cd THEN
860: x_fact_name:= ddr_webservices_constants.g_pp_fact_tbl;
861: -- for RETAIL INVENTORY ITEM DAY
862: WHEN ddr_webservices_constants.g_riid_cd THEN
863: x_fact_name:= ddr_webservices_constants.g_riid_fact_tbl;
864: -- for RETAIL SALE RETURN ITEM DAY fact
865: WHEN ddr_webservices_constants.g_rsrid_cd THEN
866: x_fact_name:= ddr_webservices_constants.g_rsrid_fact_tbl;
867: -- for RETAILER ORDER ITEM DAY

Line 865: WHEN ddr_webservices_constants.g_rsrid_cd THEN

861: -- for RETAIL INVENTORY ITEM DAY
862: WHEN ddr_webservices_constants.g_riid_cd THEN
863: x_fact_name:= ddr_webservices_constants.g_riid_fact_tbl;
864: -- for RETAIL SALE RETURN ITEM DAY fact
865: WHEN ddr_webservices_constants.g_rsrid_cd THEN
866: x_fact_name:= ddr_webservices_constants.g_rsrid_fact_tbl;
867: -- for RETAILER ORDER ITEM DAY
868: WHEN ddr_webservices_constants.g_roid_cd THEN
869: x_fact_name:= ddr_webservices_constants.g_roid_fact_tbl;

Line 866: x_fact_name:= ddr_webservices_constants.g_rsrid_fact_tbl;

862: WHEN ddr_webservices_constants.g_riid_cd THEN
863: x_fact_name:= ddr_webservices_constants.g_riid_fact_tbl;
864: -- for RETAIL SALE RETURN ITEM DAY fact
865: WHEN ddr_webservices_constants.g_rsrid_cd THEN
866: x_fact_name:= ddr_webservices_constants.g_rsrid_fact_tbl;
867: -- for RETAILER ORDER ITEM DAY
868: WHEN ddr_webservices_constants.g_roid_cd THEN
869: x_fact_name:= ddr_webservices_constants.g_roid_fact_tbl;
870: -- for RETAILER SHIP ITEM DAY

Line 868: WHEN ddr_webservices_constants.g_roid_cd THEN

864: -- for RETAIL SALE RETURN ITEM DAY fact
865: WHEN ddr_webservices_constants.g_rsrid_cd THEN
866: x_fact_name:= ddr_webservices_constants.g_rsrid_fact_tbl;
867: -- for RETAILER ORDER ITEM DAY
868: WHEN ddr_webservices_constants.g_roid_cd THEN
869: x_fact_name:= ddr_webservices_constants.g_roid_fact_tbl;
870: -- for RETAILER SHIP ITEM DAY
871: WHEN ddr_webservices_constants.g_rsid_cd THEN
872: x_fact_name:= ddr_webservices_constants.g_rsid_fact_tbl;

Line 869: x_fact_name:= ddr_webservices_constants.g_roid_fact_tbl;

865: WHEN ddr_webservices_constants.g_rsrid_cd THEN
866: x_fact_name:= ddr_webservices_constants.g_rsrid_fact_tbl;
867: -- for RETAILER ORDER ITEM DAY
868: WHEN ddr_webservices_constants.g_roid_cd THEN
869: x_fact_name:= ddr_webservices_constants.g_roid_fact_tbl;
870: -- for RETAILER SHIP ITEM DAY
871: WHEN ddr_webservices_constants.g_rsid_cd THEN
872: x_fact_name:= ddr_webservices_constants.g_rsid_fact_tbl;
873: -- for SALE FORECAST ITEM BY DAY

Line 871: WHEN ddr_webservices_constants.g_rsid_cd THEN

867: -- for RETAILER ORDER ITEM DAY
868: WHEN ddr_webservices_constants.g_roid_cd THEN
869: x_fact_name:= ddr_webservices_constants.g_roid_fact_tbl;
870: -- for RETAILER SHIP ITEM DAY
871: WHEN ddr_webservices_constants.g_rsid_cd THEN
872: x_fact_name:= ddr_webservices_constants.g_rsid_fact_tbl;
873: -- for SALE FORECAST ITEM BY DAY
874: WHEN ddr_webservices_constants.g_sfid_cd THEN
875: x_fact_name:= ddr_webservices_constants.g_sfid_fact_tbl;

Line 872: x_fact_name:= ddr_webservices_constants.g_rsid_fact_tbl;

868: WHEN ddr_webservices_constants.g_roid_cd THEN
869: x_fact_name:= ddr_webservices_constants.g_roid_fact_tbl;
870: -- for RETAILER SHIP ITEM DAY
871: WHEN ddr_webservices_constants.g_rsid_cd THEN
872: x_fact_name:= ddr_webservices_constants.g_rsid_fact_tbl;
873: -- for SALE FORECAST ITEM BY DAY
874: WHEN ddr_webservices_constants.g_sfid_cd THEN
875: x_fact_name:= ddr_webservices_constants.g_sfid_fact_tbl;
876: END CASE;

Line 874: WHEN ddr_webservices_constants.g_sfid_cd THEN

870: -- for RETAILER SHIP ITEM DAY
871: WHEN ddr_webservices_constants.g_rsid_cd THEN
872: x_fact_name:= ddr_webservices_constants.g_rsid_fact_tbl;
873: -- for SALE FORECAST ITEM BY DAY
874: WHEN ddr_webservices_constants.g_sfid_cd THEN
875: x_fact_name:= ddr_webservices_constants.g_sfid_fact_tbl;
876: END CASE;
877: EXCEPTION
878: WHEN OTHERS THEN

Line 875: x_fact_name:= ddr_webservices_constants.g_sfid_fact_tbl;

871: WHEN ddr_webservices_constants.g_rsid_cd THEN
872: x_fact_name:= ddr_webservices_constants.g_rsid_fact_tbl;
873: -- for SALE FORECAST ITEM BY DAY
874: WHEN ddr_webservices_constants.g_sfid_cd THEN
875: x_fact_name:= ddr_webservices_constants.g_sfid_fact_tbl;
876: END CASE;
877: EXCEPTION
878: WHEN OTHERS THEN
879: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

Line 879: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

875: x_fact_name:= ddr_webservices_constants.g_sfid_fact_tbl;
876: END CASE;
877: EXCEPTION
878: WHEN OTHERS THEN
879: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
880: x_msg_count:=1;
881: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
882: END get_fact_table;
883:

Line 894: WHEN ddr_webservices_constants.g_misd_cd THEN

890: BEGIN
891: --case condition to identify aggregate column names from fact table
892: CASE p_fact_code
893: -- for MARKET ITEM SALES DAY
894: WHEN ddr_webservices_constants.g_misd_cd THEN
895: x_fact_cols:='sum(x.AVG_MMACV_SLS_RATE) AVG_MMACV_SLS_RATE,'
896: ||'sum(x.AVG_STORE_SELL_ITEM_QTY) AVG_STORE_SELL_ITEM_QTY,'
897: ||'sum(x.AVG_ACV_WGT_DSTRBTN_PCT) AVG_ACV_WGT_DSTRBTN_PCT,'
898: ||'sum(x.AVG_WGT_PRICE_RDCTN_PCT) AVG_WGT_PRICE_RDCTN_PCT,'

Line 908: WHEN ddr_webservices_constants.g_pp_cd THEN

904: ||'sum(x.SLS_PRICE_CUT_AMT) SLS_PRICE_CUT_AMT,'
905: ||'sum(x.MAIN_AD_QTY) MAIN_AD_QTY,'
906: ||'sum(x.MAIN_AD_AMT) MAIN_AD_AMT';
907: --for PROMOTION PLAN
908: WHEN ddr_webservices_constants.g_pp_cd THEN
909: x_fact_cols:='sum(x.PRMTN_PRICE_AMT) PRMTN_PRICE_AMT';
910: -- for RETAIL INVENTORY ITEM DAY
911: WHEN ddr_webservices_constants.g_riid_cd THEN
912: x_fact_cols:= 'sum(x.ON_HAND_QTY) ON_HAND_QTY,'

Line 911: WHEN ddr_webservices_constants.g_riid_cd THEN

907: --for PROMOTION PLAN
908: WHEN ddr_webservices_constants.g_pp_cd THEN
909: x_fact_cols:='sum(x.PRMTN_PRICE_AMT) PRMTN_PRICE_AMT';
910: -- for RETAIL INVENTORY ITEM DAY
911: WHEN ddr_webservices_constants.g_riid_cd THEN
912: x_fact_cols:= 'sum(x.ON_HAND_QTY) ON_HAND_QTY,'
913: ||'sum(x.RECVD_QTY) RECVD_QTY,'
914: ||'sum(x.IN_TRANSIT_QTY) IN_TRANSIT_QTY,'
915: ||'sum(x.BCK_ORDR_QTY) BCK_ORDR_QTY,'

Line 928: WHEN ddr_webservices_constants.g_rsrid_cd THEN

924: ||'sum(x.IN_TRANSIT_RTL_AMT) IN_TRANSIT_RTL_AMT,'
925: ||'sum(x.BCKORDR_RTL_AMT) BCKORDR_RTL_AMT,'
926: ||'sum(x.QLTY_HOLD_RTL_AMT) QLTY_HOLD_RTL_AMT';
927: -- for RETAIL SALE RETURN ITEM DAY fact
928: WHEN ddr_webservices_constants.g_rsrid_cd THEN
929: x_fact_cols:='sum(x.SLS_QTY) SLS_QTY,'
930: ||'sum(x.SLS_AMT) SLS_AMT,'
931: ||'sum(x.SLS_COST_AMT) SLS_COST_AMT,'
932: ||'sum(x.RTRN_QTY) RTRN_QTY,'

Line 936: WHEN ddr_webservices_constants.g_roid_cd THEN

932: ||'sum(x.RTRN_QTY) RTRN_QTY,'
933: ||'sum(x.RTRN_AMT) RTRN_AMT,'
934: ||'sum(x.RTRN_COST_AMT) RTRN_COST_AMT';
935: -- for RETAILER ORDER ITEM DAY
936: WHEN ddr_webservices_constants.g_roid_cd THEN
937: x_fact_cols:='sum(x.ORDR_QTY) ORDR_QTY,'
938: ||'sum(x.ORDR_AMT) ORDR_AMT';
939: -- for RETAILER SHIP ITEM DAY
940: WHEN ddr_webservices_constants.g_rsid_cd THEN

Line 940: WHEN ddr_webservices_constants.g_rsid_cd THEN

936: WHEN ddr_webservices_constants.g_roid_cd THEN
937: x_fact_cols:='sum(x.ORDR_QTY) ORDR_QTY,'
938: ||'sum(x.ORDR_AMT) ORDR_AMT';
939: -- for RETAILER SHIP ITEM DAY
940: WHEN ddr_webservices_constants.g_rsid_cd THEN
941: x_fact_cols:= 'sum(x.SHIP_QTY) SHIP_QTY,'
942: ||'sum(x.SHIP_AMT) SHIP_AMT';
943: -- for SALE FORECAST ITEM BY DAY
944: WHEN ddr_webservices_constants.g_sfid_cd THEN

Line 944: WHEN ddr_webservices_constants.g_sfid_cd THEN

940: WHEN ddr_webservices_constants.g_rsid_cd THEN
941: x_fact_cols:= 'sum(x.SHIP_QTY) SHIP_QTY,'
942: ||'sum(x.SHIP_AMT) SHIP_AMT';
943: -- for SALE FORECAST ITEM BY DAY
944: WHEN ddr_webservices_constants.g_sfid_cd THEN
945: x_fact_cols:= 'sum(x.FRCST_SLS_QTY) FRCST_SLS_QTY,'
946: ||'sum(x.FRCST_SLS_AMT) FRCST_SLS_AMT';
947: END CASE;
948: EXCEPTION

Line 950: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

946: ||'sum(x.FRCST_SLS_AMT) FRCST_SLS_AMT';
947: END CASE;
948: EXCEPTION
949: WHEN OTHERS THEN
950: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
951: x_msg_count:=1;
952: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
953: END get_aggr_fact_colms;
954:

Line 965: WHEN ddr_webservices_constants.g_misd_cd THEN

961: BEGIN
962: --case condition to identify aggregate column names from fact table
963: CASE p_fact_code
964: -- for MARKET ITEM SALES DAY
965: WHEN ddr_webservices_constants.g_misd_cd THEN
966: x_fact_cols:='x.MFG_ORG_CD MFG_ORG_CD,'
967: ||'x.RTL_ORG_CD RTL_ORG_CD,'
968: ||'x.GLBL_ITEM_ID GLBL_ITEM_ID,'
969: ||'x.MKT_AREA_ID MKT_AREA_ID,'

Line 1013: WHEN ddr_webservices_constants.g_pp_cd THEN

1009: ||'x.MAIN_AD_AMT_RPT MAIN_AD_AMT_RPT';
1010: --for PROMOTION PLAN
1011: --as there is a date range for promotion records, they would get selected for every day
1012: --therefore DISTINCT clause is added to ensure query doesn't return duplicates
1013: WHEN ddr_webservices_constants.g_pp_cd THEN
1014: x_fact_cols:='DISTINCT x.MFG_ORG_CD MFG_ORG_CD,'
1015: ||'x.RTL_ORG_CD RTL_ORG_CD,'
1016: ||'x.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID,'
1017:

Line 1030: WHEN ddr_webservices_constants.g_riid_cd THEN

1026: ||'x.PRMTN_PRICE_AMT PRMTN_PRICE_AMT,'
1027: ||'x.PRMTN_PRICE_AMT_LCL PRMTN_PRICE_AMT_LCL,'
1028: ||'x.PRMTN_PRICE_AMT_RPT PRMTN_PRICE_AMT_RPT';
1029: -- for RETAIL INVENTORY ITEM DAY
1030: WHEN ddr_webservices_constants.g_riid_cd THEN
1031: x_fact_cols:= 'x.MFG_ORG_CD MFG_ORG_CD,'
1032: ||'x.RTL_ORG_CD RTL_ORG_CD,'
1033: ||'x.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID,'
1034:

Line 1093: WHEN ddr_webservices_constants.g_rsrid_cd THEN

1089: ||'x.QLTY_HOLD_RTL_AMT QLTY_HOLD_RTL_AMT,'
1090: ||'x.QLTY_HOLD_RTL_AMT_LCL QLTY_HOLD_RTL_AMT_LCL,'
1091: ||'x.QLTY_HOLD_RTL_AMT_RPT QLTY_HOLD_RTL_AMT_RPT';
1092: -- for RETAIL SALE RETURN ITEM DAY fact
1093: WHEN ddr_webservices_constants.g_rsrid_cd THEN
1094: x_fact_cols:='x.MFG_ORG_CD MFG_ORG_CD,'
1095: ||'x.RTL_ORG_CD RTL_ORG_CD,'
1096: ||'x.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID,'
1097:

Line 1126: WHEN ddr_webservices_constants.g_roid_cd THEN

1122: ||'x.RTRN_COST_AMT RTRN_COST_AMT,'
1123: ||'x.RTRN_COST_AMT_LCL RTRN_COST_AMT_LCL,'
1124: ||'x.RTRN_COST_AMT_RPT RTRN_COST_AMT_RPT';
1125: -- for RETAILER ORDER ITEM DAY
1126: WHEN ddr_webservices_constants.g_roid_cd THEN
1127: x_fact_cols:='x.MFG_ORG_CD MFG_ORG_CD,'
1128: ||'x.RTL_ORG_CD RTL_ORG_CD,'
1129: ||'x.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID,'
1130:

Line 1147: WHEN ddr_webservices_constants.g_rsid_cd THEN

1143: ||'x.ORDR_AMT ORDR_AMT,'
1144: ||'x.ORDR_AMT_LCL ORDR_AMT_LCL,'
1145: ||'x.ORDR_AMT_RPT ORDR_AMT_RPT';
1146: -- for RETAILER SHIP ITEM DAY
1147: WHEN ddr_webservices_constants.g_rsid_cd THEN
1148: x_fact_cols:= 'x.MFG_ORG_CD MFG_ORG_CD,'
1149: ||'x.RTL_ORG_CD RTL_ORG_CD,'
1150: ||'x.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID,'
1151:

Line 1168: WHEN ddr_webservices_constants.g_sfid_cd THEN

1164: ||'x.SHIP_AMT SHIP_AMT,'
1165: ||'x.SHIP_AMT_LCL SHIP_AMT_LCL,'
1166: ||'x.SHIP_AMT_RPT SHIP_AMT_RPT';
1167: -- for SALE FORECAST ITEM BY DAY
1168: WHEN ddr_webservices_constants.g_sfid_cd THEN
1169: x_fact_cols:= 'x.MFG_ORG_CD MFG_ORG_CD,'
1170: ||'x.RTL_ORG_CD RTL_ORG_CD,'
1171: ||'x.FRCST_NBR FRCST_NBR,'
1172: ||'x.FRCST_TYP FRCST_TYP,'

Line 1192: x_return_status:=ddr_webservices_constants.g_ret_sts_success;

1188: ||'x.FRCST_SLS_AMT FRCST_SLS_AMT,'
1189: ||'x.FRCST_SLS_AMT_LCL FRCST_SLS_AMT_LCL,'
1190: ||'x.FRCST_SLS_AMT_RPT FRCST_SLS_AMT_RPT';
1191: END CASE;
1192: x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1193: EXCEPTION
1194: WHEN OTHERS THEN
1195: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1196: x_msg_count:=1;

Line 1195: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1191: END CASE;
1192: x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1193: EXCEPTION
1194: WHEN OTHERS THEN
1195: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1196: x_msg_count:=1;
1197: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1198: END get_detail_fact_colms;
1199:

Line 1217: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

1213: BEGIN
1214: --get aggregate columns for the Organization hierarchy based on given hierarchy level code
1215: IF p_exp_org_level IS NOT NULL THEN
1216: get_hrchy_lvl('ORGANIZATION',p_exp_org_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1217: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
1218: RETURN;
1219: END IF;
1220: get_org_ref_join(l_lvl_rnk,l_group_col,x_return_status, x_msg_count,x_msg_data);
1221: END IF;

Line 1225: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

1221: END IF;
1222: --get aggregate columns for the location hierarchy based on given hierarchy level code
1223: IF p_exp_loc_level IS NOT NULL THEN
1224: get_hrchy_lvl('LOCATION',p_exp_loc_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1225: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
1226: RETURN;
1227: END IF;
1228: get_loc_ref_join(l_lvl_rnk,l_ref_join,x_return_status, x_msg_count,x_msg_data);
1229: IF l_group_col IS NOT NUll THEN

Line 1238: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

1234: END IF;
1235: --get aggregate columns for the Item hierarchy based on given hierarchy level code
1236: IF p_exp_item_level IS NOT NULL THEN
1237: get_hrchy_lvl('ITEM',p_exp_item_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1238: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
1239: RETURN;
1240: END IF;
1241: get_item_ref_join(l_lvl_rnk,l_ref_join,x_return_status, x_msg_count,x_msg_data);
1242: IF l_group_col IS NOT NUll THEN

Line 1252: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN

1248: --get aggregate columns for the time hierarchy based on given hierarchy level code
1249: IF p_exp_time_level IS NOT NULL THEN
1250: SELECT hrchy_lvl_name INTO l_hrchy_lvl_name FROM DDR_WS_METADATA WHERE hrchy_lvl_cd=p_exp_time_level;
1251: get_hrchy_lvl(l_hrchy_lvl_name,p_exp_time_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1252: IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
1253: RETURN;
1254: END IF;
1255: get_time_ref_join(l_hrchy_lvl_name,l_lvl_rnk,l_ref_join,x_return_status, x_msg_count,x_msg_data);
1256: IF l_group_col IS NOT NUll THEN

Line 1263: x_return_status:=ddr_webservices_constants.g_ret_sts_success;

1259: l_group_col :=l_ref_join;
1260: END IF;
1261: END IF;
1262: x_group_col:= l_group_col;
1263: x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1264: EXCEPTION
1265: WHEN NO_DATA_FOUND THEN
1266: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1267: x_msg_count:=1;

Line 1266: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1262: x_group_col:= l_group_col;
1263: x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1264: EXCEPTION
1265: WHEN NO_DATA_FOUND THEN
1266: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1267: x_msg_count:=1;
1268: x_msg_data:='No Data Found. Error code:'||sqlcode||' Error message:'||sqlerrm;
1269: WHEN OTHERS THEN
1270: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

Line 1270: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1266: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1267: x_msg_count:=1;
1268: x_msg_data:='No Data Found. Error code:'||sqlcode||' Error message:'||sqlerrm;
1269: WHEN OTHERS THEN
1270: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1271: x_msg_count:=1;
1272: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1273: END get_aggr_group_colms;
1274:

Line 1313: l_xml_file := UTL_FILE.fopen(l_dir_name,l_file_name,ddr_webservices_constants.g_file_write_mode);

1309: l_file_id :=get_ddr_ws_file_seq_nextval(x_return_status,x_msg_count,x_msg_data);
1310: --construct file name
1311: l_file_name := p_fact_code || '_'|| l_file_id || '.xml';
1312: --open file in write mode
1313: l_xml_file := UTL_FILE.fopen(l_dir_name,l_file_name,ddr_webservices_constants.g_file_write_mode);
1314: --write xml header data in the xml file
1315: UTL_FILE.put_line(l_xml_file, '');
1316: --open cursor
1317: l_cur := dbms_sql.open_cursor;

Line 1352: VALUES (l_file_id, p_job_id, l_file_name, ddr_webservices_constants.g_ret_sts_success, 'N','ABC', SYSDATE, 'ABC', 'ABC',101, SYSDATE, 101,

1348: BEGIN
1349: INSERT INTO ddr_ws_job_file_dls(file_id, job_id, file_name, status, delete_flag,src_sys_idnt, src_sys_dt, crtd_by_dsr, last_updt_by_dsr,
1350: created_by, creation_date, last_updated_by,
1351: last_update_date, last_update_login)
1352: VALUES (l_file_id, p_job_id, l_file_name, ddr_webservices_constants.g_ret_sts_success, 'N','ABC', SYSDATE, 'ABC', 'ABC',101, SYSDATE, 101,
1353: SYSDATE, 101);
1354: END;
1355: l_fetch_rows:=dbms_sql.fetch_rows(l_cur);
1356: IF(l_fetch_rows > 0) THEN

Line 1362: l_xml_file := UTL_FILE.fopen(l_dir_name,l_file_name,ddr_webservices_constants.g_file_write_mode);

1358: RAISE l_max_rows;
1359: END IF;
1360: l_file_id :=get_ddr_ws_file_seq_nextval(x_return_status,x_msg_count,x_msg_data);
1361: l_file_name := p_fact_code || '_'|| l_file_id || '.xml';
1362: l_xml_file := UTL_FILE.fopen(l_dir_name,l_file_name,ddr_webservices_constants.g_file_write_mode);
1363: UTL_FILE.put_line(l_xml_file, '');
1364: UTL_FILE.put_line(l_xml_file, '');
1365: l_fetch_ctn:=0;
1366: END IF;

Line 1385: VALUES (l_file_id, p_job_id, l_file_name, ddr_webservices_constants.g_ret_sts_success, 'N',

1381: src_sys_idnt, src_sys_dt, crtd_by_dsr, last_updt_by_dsr,
1382: created_by, creation_date, last_updated_by,
1383: last_update_date, last_update_login
1384: )
1385: VALUES (l_file_id, p_job_id, l_file_name, ddr_webservices_constants.g_ret_sts_success, 'N',
1386: 'ABC', SYSDATE, 'ABC', 'ABC',101, SYSDATE, 101,
1387: SYSDATE, 101);
1388: END;
1389: END IF;

Line 1390: x_return_status:=ddr_webservices_constants.g_ret_sts_success;

1386: 'ABC', SYSDATE, 'ABC', 'ABC',101, SYSDATE, 101,
1387: SYSDATE, 101);
1388: END;
1389: END IF;
1390: x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1391: EXCEPTION
1392: WHEN l_max_rows THEN
1393: IF dbms_sql.is_open(l_cur) THEN
1394: dbms_sql.close_cursor(l_cur);

Line 1399: x_return_status:=ddr_webservices_constants.g_ret_sts_error;

1395: END IF;
1396: IF UTL_FILE.is_open(l_xml_file) THEN
1397: UTL_FILE.fclose(l_xml_file);
1398: END IF;
1399: x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1400: x_msg_count:=1;
1401: x_msg_data:='Program exceeded maximum row OUT NOCOPY put';
1402: WHEN UTL_FILE.INTERNAL_ERROR THEN
1403: IF dbms_sql.is_open(l_cur) THEN

Line 1409: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1405: END IF;
1406: IF UTL_FILE.is_open(l_xml_file) THEN
1407: UTL_FILE.fclose(l_xml_file);
1408: END IF;
1409: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1410: x_msg_count:=1;
1411: x_msg_data:='Cannot open file :' || l_file_name ||', write error; code:' || sqlcode ||',message:' || sqlerrm;
1412: WHEN UTL_FILE.INVALID_OPERATION THEN
1413: IF dbms_sql.is_open(l_cur) THEN

Line 1419: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1415: END IF;
1416: IF UTL_FILE.is_open(l_xml_file) THEN
1417: UTL_FILE.fclose(l_xml_file);
1418: END IF;
1419: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1420: x_msg_count:=1;
1421: x_msg_data:='Cannot open file :' || l_file_name ||', write error; code:' || sqlcode ||',message:' || sqlerrm;
1422: WHEN UTL_FILE.INVALID_PATH THEN
1423: IF dbms_sql.is_open(l_cur) THEN

Line 1429: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1425: END IF;
1426: IF UTL_FILE.is_open(l_xml_file) THEN
1427: UTL_FILE.fclose(l_xml_file);
1428: END IF;
1429: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1430: x_msg_count:=1;
1431: x_msg_data:='Cannot open file :' || l_file_name ||', write error; code:' || sqlcode ||',message:' || sqlerrm;
1432: WHEN UTL_FILE.WRITE_ERROR THEN
1433: IF dbms_sql.is_open(l_cur) THEN

Line 1439: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1435: END IF;
1436: IF UTL_FILE.is_open(l_xml_file) THEN
1437: UTL_FILE.fclose(l_xml_file);
1438: END IF;
1439: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1440: x_msg_count:=1;
1441: x_msg_data:='Cannot write to file :' || l_file_name ||', write error; code:' || sqlcode ||',message:' || sqlerrm;
1442: WHEN OTHERS THEN
1443: IF dbms_sql.is_open(l_cur) THEN

Line 1449: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1445: END IF;
1446: IF UTL_FILE.is_open(l_xml_file) THEN
1447: UTL_FILE.fclose(l_xml_file);
1448: END IF;
1449: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1450: x_msg_count:=1;
1451: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1452: END write_fact_to_xml_file;
1453:

Line 1484: IF p_api_version<>ddr_webservices_constants.g_api_version THEN

1480: BEGIN
1481: IF p_api_version IS NULL THEN
1482: RAISE l_api_ver;
1483: END IF;
1484: IF p_api_version<>ddr_webservices_constants.g_api_version THEN
1485: RAISE l_api_ver;
1486: END IF;
1487: IF p_fact_code IS NULL THEN
1488: RAISE l_fact_code_null;

Line 1496: IF p_fact_code = ddr_webservices_constants.g_pp_cd AND p_exp_time_level IS NOT NULL THEN

1492: END IF;
1493: IF p_org_cd IS NULL THEN
1494: RAISE l_rtl_org_code_null;
1495: END IF;
1496: IF p_fact_code = ddr_webservices_constants.g_pp_cd AND p_exp_time_level IS NOT NULL THEN
1497: RAISE l_pp_invld_aggr;
1498: END IF;
1499: x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1500: EXCEPTION

Line 1499: x_return_status:=ddr_webservices_constants.g_ret_sts_success;

1495: END IF;
1496: IF p_fact_code = ddr_webservices_constants.g_pp_cd AND p_exp_time_level IS NOT NULL THEN
1497: RAISE l_pp_invld_aggr;
1498: END IF;
1499: x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1500: EXCEPTION
1501: WHEN l_api_ver THEN
1502: x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1503: x_msg_count:=1;

Line 1502: x_return_status:=ddr_webservices_constants.g_ret_sts_error;

1498: END IF;
1499: x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1500: EXCEPTION
1501: WHEN l_api_ver THEN
1502: x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1503: x_msg_count:=1;
1504: x_msg_data:='API version number should not be null';
1505: WHEN l_fact_code_null THEN
1506: x_return_status:=ddr_webservices_constants.g_ret_sts_error;

Line 1506: x_return_status:=ddr_webservices_constants.g_ret_sts_error;

1502: x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1503: x_msg_count:=1;
1504: x_msg_data:='API version number should not be null';
1505: WHEN l_fact_code_null THEN
1506: x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1507: x_msg_count:=1;
1508: x_msg_data:='Fact code should not be null';
1509: WHEN l_mfg_code_null THEN
1510: x_return_status:=ddr_webservices_constants.g_ret_sts_error;

Line 1510: x_return_status:=ddr_webservices_constants.g_ret_sts_error;

1506: x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1507: x_msg_count:=1;
1508: x_msg_data:='Fact code should not be null';
1509: WHEN l_mfg_code_null THEN
1510: x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1511: x_msg_count:=1;
1512: x_msg_data:='Manufacturer Organization code should not be null';
1513: WHEN l_rtl_org_code_null THEN
1514: x_return_status:=ddr_webservices_constants.g_ret_sts_error;

Line 1514: x_return_status:=ddr_webservices_constants.g_ret_sts_error;

1510: x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1511: x_msg_count:=1;
1512: x_msg_data:='Manufacturer Organization code should not be null';
1513: WHEN l_rtl_org_code_null THEN
1514: x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1515: x_msg_count:=1;
1516: x_msg_data:='Retailer Organization code should not be null';
1517: WHEN l_pp_invld_aggr THEN
1518: x_return_status:=ddr_webservices_constants.g_ret_sts_error;

Line 1518: x_return_status:=ddr_webservices_constants.g_ret_sts_error;

1514: x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1515: x_msg_count:=1;
1516: x_msg_data:='Retailer Organization code should not be null';
1517: WHEN l_pp_invld_aggr THEN
1518: x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1519: x_msg_count:=1;
1520: x_msg_data:='Time based aggregation is not supported for Promotion Plan data';
1521: WHEN OTHERS THEN
1522: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

Line 1522: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1518: x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1519: x_msg_count:=1;
1520: x_msg_data:='Time based aggregation is not supported for Promotion Plan data';
1521: WHEN OTHERS THEN
1522: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1523: x_msg_count:=1;
1524: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1525: END validate_input_params;
1526:

Line 1535: x_return_status:=ddr_webservices_constants.g_ret_sts_success;

1531: x_sys_var_val OUT NOCOPY VARCHAR2)
1532: IS
1533: BEGIN
1534: SELECT lkup_name INTO x_sys_var_val FROM ddr_r_lkup_mst WHERE lkup_cd=p_sys_var;
1535: x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1536: EXCEPTION
1537: WHEN NO_DATA_FOUND THEN
1538: x_return_status := ddr_webservices_constants.g_ret_sts_error;
1539: x_msg_count := 1;

Line 1538: x_return_status := ddr_webservices_constants.g_ret_sts_error;

1534: SELECT lkup_name INTO x_sys_var_val FROM ddr_r_lkup_mst WHERE lkup_cd=p_sys_var;
1535: x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1536: EXCEPTION
1537: WHEN NO_DATA_FOUND THEN
1538: x_return_status := ddr_webservices_constants.g_ret_sts_error;
1539: x_msg_count := 1;
1540: x_msg_data := 'No Data Found. Error Code' ||sqlcode||' Error message:'||sqlerrm;
1541: WHEN OTHERS THEN
1542: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

Line 1542: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1538: x_return_status := ddr_webservices_constants.g_ret_sts_error;
1539: x_msg_count := 1;
1540: x_msg_data := 'No Data Found. Error Code' ||sqlcode||' Error message:'||sqlerrm;
1541: WHEN OTHERS THEN
1542: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1543: x_msg_count:=1;
1544: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1545: END get_sys_var_val;
1546:

Line 1557: x_return_status := ddr_webservices_constants.g_ret_sts_error;

1553: SELECT DDR_WS_FILE_SEQ.NEXTVAL INTO l_next_val FROM dual;
1554: RETURN to_char(l_next_val);
1555: EXCEPTION
1556: WHEN NO_DATA_FOUND THEN
1557: x_return_status := ddr_webservices_constants.g_ret_sts_error;
1558: x_msg_count := 1;
1559: x_msg_data := 'No Data Found. Error Code' ||sqlcode||' Error message:'||sqlerrm;
1560: WHEN OTHERS THEN
1561: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

Line 1561: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1557: x_return_status := ddr_webservices_constants.g_ret_sts_error;
1558: x_msg_count := 1;
1559: x_msg_data := 'No Data Found. Error Code' ||sqlcode||' Error message:'||sqlerrm;
1560: WHEN OTHERS THEN
1561: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1562: x_msg_count:=1;
1563: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1564: END get_ddr_ws_file_seq_nextval;
1565:

Line 1581: --WHEN ddr_webservices_constants.g_rsrid_cd THEN

1577: l_lvl_rnk NUMBER:=null;
1578: BEGIN
1579: -- CASE p_fact_code
1580: -- for RETAIL SALE RETURN ITEM DAY fact
1581: --WHEN ddr_webservices_constants.g_rsrid_cd THEN
1582:
1583: IF p_item_dim_lvl_cd IS NOT NULL THEN
1584: get_hrchy_lvl('ITEM',p_item_dim_lvl_cd,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1585: ELSIF p_exp_item_level IS NOT NULL THEN

Line 1622: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1618: END IF;
1619: -- END CASE;
1620: EXCEPTION
1621: WHEN OTHERS THEN
1622: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1623: x_msg_count:=1;
1624: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1625: END get_itm_hrchy_clauses;
1626:

Line 1650: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1646: x_ref_join:=' ITMF.MFG_DIV_CD ';
1647: END CASE;
1648: EXCEPTION
1649: WHEN OTHERS THEN
1650: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1651: x_msg_count:=1;
1652: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1653:
1654: END get_item_ref_join;

Line 1672: --WHEN ddr_webservices_constants.g_rsrid_cd THEN

1668: l_lvl_rnk NUMBER:=null;
1669: BEGIN
1670: -- CASE p_fact_code
1671: -- for RETAIL SALE RETURN ITEM DAY fact
1672: --WHEN ddr_webservices_constants.g_rsrid_cd THEN
1673: IF p_org_dim_lvl_cd IS NOT NULL THEN
1674: get_hrchy_lvl('ORGANIZATION',p_org_dim_lvl_cd,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1675: ELSIF p_exp_org_level IS NOT NULL THEN
1676: get_hrchy_lvl('ORGANIZATION',p_exp_org_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);

Line 1716: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1712: END IF;
1713: -- END CASE;
1714: EXCEPTION
1715: WHEN OTHERS THEN
1716: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1717: x_msg_count:=1;
1718: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1719: END get_org_hrchy_clauses;
1720:

Line 1744: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1740: x_ref_join:=' ORGG.CLSTR_CD ';
1741: END CASE;
1742: EXCEPTION
1743: WHEN OTHERS THEN
1744: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1745: x_msg_count:=1;
1746: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1747: END get_org_ref_join;
1748:

Line 1766: --WHEN ddr_webservices_constants.g_rsrid_cd THEN

1762: l_hrchy_lvl_name VARCHAR2(50):=null;
1763: BEGIN
1764: -- CASE p_fact_code
1765: -- for RETAIL SALE RETURN ITEM DAY fact
1766: --WHEN ddr_webservices_constants.g_rsrid_cd THEN
1767: --to get the time hierarchy level name(GREGORIAN TIME OR BUSINESS TIME)
1768: IF p_time_dim_lvl_cd IS NOT NULL THEN
1769: SELECT hrchy_lvl_name INTO l_hrchy_lvl_name FROM DDR_WS_METADATA WHERE
1770: hrchy_lvl_cd=p_time_dim_lvl_cd;

Line 1784: IF p_fact_code = ddr_webservices_constants.g_pp_cd THEN

1780: CASE l_hrchy_lvl_name
1781: WHEN 'GREGORIAN TIME' THEN
1782: IF l_lvl_rnk>=1 THEN
1783: x_time_ref_tbls:=',DDR_R_DAY TDAY';
1784: IF p_fact_code = ddr_webservices_constants.g_pp_cd THEN
1785: x_time_ref_joins:=' AND TDAY.CLNDR_DT BETWEEN X.PRMTN_FROM_DT AND X.PRMTN_TO_DT';
1786: ELSE
1787: x_time_ref_joins:=' AND X.DAY_CD = TDAY.DAY_CD';
1788: END IF;

Line 1807: IF p_fact_code = ddr_webservices_constants.g_pp_cd THEN

1803: WHEN 'BUSINESS TIME' THEN
1804: IF l_lvl_rnk>=1 THEN
1805: x_time_ref_tbls:=',DDR_R_CLNDR TCLNDR,DDR_R_DAY TDAY,DDR_R_BASE_DAY TBA';
1806:
1807: IF p_fact_code = ddr_webservices_constants.g_pp_cd THEN
1808: x_time_ref_joins:=' AND TDAY.CLNDR_DT BETWEEN X.PRMTN_FROM_DT AND X.PRMTN_TO_DT AND TDAY.DAY_CD = TBA.DAY_CD '
1809: ||' AND TBA.CLNDR_TYP=''BSNS'' AND TBA.CLNDR_CD=TCLNDR.CLNDR_CD AND TCLNDR.CLNDR_TYP=''BSNS'''
1810: ||' AND TCLNDR.ORG_CD='''||p_org_cd||'''';
1811: ELSE

Line 1843: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1839: END CASE;
1840: -- END CASE;
1841: EXCEPTION
1842: WHEN OTHERS THEN
1843: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1844: x_msg_count:=1;
1845: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1846: END get_time_hrchy_clauses;
1847:

Line 1884: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1880: END CASE;
1881: END CASE;
1882: EXCEPTION
1883: WHEN OTHERS THEN
1884: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1885: x_msg_count:=1;
1886: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1887: END get_time_ref_join;
1888:

Line 1907: --WHEN ddr_webservices_constants.g_rsrid_cd THEN

1903: l_org_lvl_rnk NUMBER:=null;
1904: BEGIN
1905: -- CASE p_fact_code
1906: -- for RETAIL SALE RETURN ITEM DAY fact
1907: --WHEN ddr_webservices_constants.g_rsrid_cd THEN
1908: IF p_loc_dim_lvl_cd IS NOT NULL THEN
1909: get_hrchy_lvl('LOCATION',p_loc_dim_lvl_cd,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1910: ELSIF p_exp_loc_level IS NOT NULL THEN
1911: get_hrchy_lvl('LOCATION',p_exp_loc_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);

Line 1940: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1936: END IF;
1937: -- END CASE;
1938: EXCEPTION
1939: WHEN OTHERS THEN
1940: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1941: x_msg_count:=1;
1942: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1943: END get_loc_hrchy_clauses;
1944:

Line 1962: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

1958: x_ref_join:=' LOCD.CNTRY_CD ';
1959: END CASE;
1960: EXCEPTION
1961: WHEN OTHERS THEN
1962: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1963: x_msg_count:=1;
1964: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1965: END get_loc_ref_join;
1966:

Line 1981: x_return_status := ddr_webservices_constants.g_ret_sts_error;

1977: -- DBMS_OUTPUT.PUT_LINE('p_hrchy_lvl_name='||p_hrchy_lvl_name||' ,p_hrchy_lvl_cd='||p_hrchy_lvl_cd||' ,x_hrchy_lvl='||l_hrchy_lvl);
1978: x_hrchy_lvl:=l_hrchy_lvl;
1979: EXCEPTION
1980: WHEN NO_DATA_FOUND THEN
1981: x_return_status := ddr_webservices_constants.g_ret_sts_error;
1982: x_msg_count := 1;
1983: x_msg_data := 'No data found for hierarchy code:'|| p_hrchy_lvl_cd ||'. Error Code' ||sqlcode||' Error message:'||sqlerrm;
1984: END get_hrchy_lvl;
1985:

Line 1998: IF p_fact_code= ddr_webservices_constants.g_sfid_cd THEN

1994: l_max_frcst_date DATE:=null;
1995: BEGIN
1996: --if the fact code is for forcest sales table, then join condition to fetch
1997: --the latest forcest version
1998: IF p_fact_code= ddr_webservices_constants.g_sfid_cd THEN
1999: --bug 6905930 change start
2000: l_join_cndn:= ' AND (x.frcst_vrsn,x.mfg_org_cd,x.rtl_org_cd,
2001: x.org_bsns_unit_id, x.day_cd, x.glbl_item_id, x.rtl_sku_item_id) IN(SELECT
2002: MAX(frcst_vrsn), mfg_org_cd, rtl_org_cd, org_bsns_unit_id, day_cd,

Line 2011: x_return_status := ddr_webservices_constants.g_ret_sts_error;

2007: END IF;
2008: x_oth_join_codn := l_join_cndn;
2009: EXCEPTION
2010: WHEN NO_DATA_FOUND THEN
2011: x_return_status := ddr_webservices_constants.g_ret_sts_error;
2012: x_msg_count := 1;
2013: x_msg_data := 'No data found. Error Code' ||sqlcode||' Error
2014: message:'||sqlerrm;
2015: WHEN OTHERS THEN

Line 2016: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;

2012: x_msg_count := 1;
2013: x_msg_data := 'No data found. Error Code' ||sqlcode||' Error
2014: message:'||sqlerrm;
2015: WHEN OTHERS THEN
2016: x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
2017: x_msg_count:=1;
2018: x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:
2019: '||sqlerrm;
2020: END get_other_join_conditions;