Asn Appointment.docx

ASN Appointment

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_APPOINTMENT_V" ("TASK_NUMBER", "SUBJECT", "TYPE", "TASK_TYPE_ID", "TASK_STATUS", "TASK_STATUS_ID", "SCHEDULED_START_DATE", "SCHEDULED_END_DATE", "PLANNED_START_DATE", "PLANNED_END_DATE", "ACTUAL_START_DATE", "ACTUAL_END_DATE", "OWNER_ID", "NAME", "OWNER_TYPE_CODE", "OWNER_TYPE", "CUSTOMER_ID", "CUSTOMER_NAME", "CUSTOMER_NUMBER", "CREATED_BY", "CREATED_BY_ID", "MEETING_MODE", "DIAL_IN", "CREATION_DATE", "COLLAB_ID", "USER_ID", "TASK_ID", "COPYRIGHT") AS SELECT JTV.TASK_NUMBER, JTV.TASK_NAME SUBJECT, JTV.TASK_TYPE TYPE, JTV.TASK_TYPE_ID, JTV.TASK_STATUS , JTV.TASK_STATUS_ID, JTV.SCHEDULED_START_DATE, JTV.SCHEDULED_END_DATE, JTV.PLANNED_START_DATE, JTV.PLANNED_END_DATE, JTV.ACTUAL_START_DATE, JTV.ACTUAL_END_DATE, JTV.OWNER_ID, JTV.OWNER NAME, JTV.OWNER_TYPE_CODE, JTV.OWNER_TYPE, JTV.CUSTOMER_ID, JTV.CUSTOMER_NAME, JTV.CUSTOMER_NUMBER, FU.USER_NAME CREATED_BY, JTV.CREATED_BY CREATED_BY_ID, CVCDV.MEETING_MODE,

CVCDV.DIAL_IN, JTV.CREATION_DATE, ---Primary Keys CVCDV.COLLAB_ID, FU.USER_ID, JTV.TASK_ID , 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.' Copyright --descr#flexfield#start --descr#flexfield#end --gl#accountff#start --gl#accountff#end FROM FND_USER FU, JTF_TASKS_V JTV, CAC_VIEW_COLLAB_DETAILS_VL CVCDV WHERE 1 AND JTV.CREATED_BY AND JTV.TASK_ID

=1 =FU.USER_ID =CVCDV.TASK_ID

AND JTV.SOURCE_OBJECT_TYPE_CODE='APPOINTMENT' ;

EIS_ASN_COMM_PLAN_ELEMENTS_V

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_COMM_PLAN_ELEMENTS_V" ("OPERATING_UNIT", "RESOURCE_NAME", "PAYMENT_BATCH", "PLAN_ELEMENT_NAME", "COMMISSION_AMOUNT", "INCENTIVE_TYPE", "DIRECT_CREDIT_RECEIVER", "TRX_TYPE_CODE", "PROCESSED_DATE", "PAYEE_SALESREP_ID", "PAYRUN_ID", "QUOTA_ID", "CP_ORG_ID", "CPT_PAYMENT_TRANSACTION_ID", "CPT_ORG_ID", "CQ_QUOTA_ID", "CQ_ORG_ID", "SALESREP_ID", "CS_ORG_ID", "HOU_ORGANIZATION_ID", "COPYRIGHT") AS select HOU.name

OPERATING_UNIT,

CS.name

RESOURCE_NAME,

CP.NAME

PAYMENT_BATCH,

CQ.name

PLAN_ELEMENT_NAME,

NVL(TO_CHAR(CPT.AMOUNT,'9999999999999.99'),0) COMMISSION_AMOUNT, CPT.INCENTIVE_TYPE_CODE CS.name

INCENTIVE_TYPE, DIRECT_CREDIT_RECEIVER,

CPT.TRX_TYPE

TRX_TYPE_CODE,

CPT.PROCESSED_DATE

PROCESSED_DATE,

CPT.PAYEE_SALESREP_ID

PAYEE_SALESREP_ID,

CPT.PAYRUN_ID

PAYRUN_ID,

CPT.QUOTA_ID, CP.ORG_ID CP_ORG_ID, -----Primary Keys CPT.PAYMENT_TRANSACTION_ID CPT_PAYMENT_TRANSACTION_ID, CPT.ORG_ID CPT_ORG_ID, CQ.QUOTA_ID CQ_QUOTA_ID, CQ.ORG_ID CQ_ORG_ID, CS.SALESREP_ID,

CS.ORG_ID CS_ORG_ID, HOU.ORGANIZATION_ID HOU_ORGANIZATION_ID , 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.' Copyright --descr#flexfield#start --descr#flexfield#end --gl#accountff#start --gl#accountff#end from CN_PAYMENT_TRANSACTIONS CN_QUOTAS

CQ,

CN_SALESREPS

CS,

CN_PAYRUNS

CP,

HR_OPERATING_UNITS

CPT,

HOU

where CPT.QUOTA_ID

= CQ.QUOTA_ID(+)

and CPT.CREDITED_SALESREP_ID = CS.SALESREP_ID and CPT.PAYRUN_ID

= CP.PAYRUN_ID

and HOU.ORGANIZATION_ID

= CS.ORG_ID (+)

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_COMM_STMT_BAL_SUMM_V" ("OPERATING_UNIT", "RESOURCE_NAME", "PAYMENT_BATCH", "BEGINING_BALANCE", "EARNINGS", "RECOVERABLE_AMT", "NONRECOVERABLE", "PAYMENT_DUE", "END_BALANCE", "SALESREP_ID", "PAYRUN_ID", "CS_SALESREP_ID", "CS_ORG_ID", "CP_NAME", "CP_ORG_ID", "HOU_ORGANIZATION_ID", "COPYRIGHT") AS select V.OPERATING_UNIT OPERATING_UNIT, V.SALES_PERSON resource_name, V.PAYMENT_BATCH PAYMENT_BATCH, V.BEGIN_BALANCE BEGINING_BALANCE, V.EARNINGS EARNINGS, V.REC_DRAW_PD recoverable_amt , V.NREC_DRAW_PD nonrecoverable , V.BEGIN_BALANCE +V.EARNINGS+V.REC_DRAW_PD+V.NREC_DRAW_PD PAYMENT_DUE, -V.REC_DRAW_PD END_BALANCE, V.SALESREP_ID, V.PAYRUN_ID, -----Primary Keys V.CS_SALESREP_ID CS_SALESREP_ID, V.CS_ORG_ID CS_ORG_ID, v.CP_NAME CP_NAME, v.CP_ORG_ID CP_ORG_ID, v.HOU_ORGANIZATION_ID HOU_ORGANIZATION_ID , 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.' Copyright --descr#flexfield#start --descr#flexfield#end --gl#accountff#start --gl#accountff#end FROM

(SELECT WK.HELD_AMOUNT, (SELECT NVL(SUM(AMOUNT),0) FROM CN_PAYMENT_TRANSACTIONS WHERE CREDITED_SALESREP_ID=WK.SALESREP_ID AND PAYRUN_ID

=WK.PAYRUN_ID

AND INCENTIVE_TYPE_CODE IN( 'BONUS','COMMISSION') AND PAY_PERIOD_ID

<

(SELECT PAY_PERIOD_ID FROM CN_PAYRUNS WHERE PAYRUN_ID=WK.PAYRUN_ID ) )+ (SELECT NVL(SUM(AMOUNT),0) FROM CN_PAYMENT_TRANSACTIONS WHERE CREDITED_SALESREP_ID=WK.SALESREP_ID AND PAYRUN_ID

=WK.PAYRUN_ID

AND INCENTIVE_TYPE_CODE = 'PMTPLN_REC' ) BEGIN_BALANCE, (SELECT NVL(SUM(AMOUNT),0) FROM CN_PAYMENT_TRANSACTIONS WHERE CREDITED_SALESREP_ID=WK.SALESREP_ID AND PAYRUN_ID

=WK.PAYRUN_ID

AND INCENTIVE_TYPE_CODE IN( 'BONUS','COMMISSION') AND PAY_PERIOD_ID

=

(SELECT PAY_PERIOD_ID FROM CN_PAYRUNS WHERE PAYRUN_ID=WK.PAYRUN_ID ) )EARNINGS, WK.PMT_AMOUNT_ADJ_REC + (SELECT NVL(SUM(PAYMENT_AMOUNT),0) FROM CN_PAYMENT_TRANSACTIONS WHERE CREDITED_SALESREP_ID=WK.SALESREP_ID

AND PAYRUN_ID

=WK.PAYRUN_ID

AND INCENTIVE_TYPE_CODE = 'MANUAL_PAY_ADJ' AND RECOVERABLE_FLAG )

='Y'

+

(SELECT ( NVL(SUM(PAYMENT_AMOUNT),0) - NVL(SUM(AMOUNT),0)) FROM CN_PAYMENT_TRANSACTIONS WHERE CREDITED_SALESREP_ID=WK.SALESREP_ID AND PAYRUN_ID

=WK.PAYRUN_ID

AND INCENTIVE_TYPE_CODE IN( 'BONUS','COMMISSION') ) REC_DRAW_PD, WK.PMT_AMOUNT_ADJ_NREC + (SELECT NVL(SUM(PAYMENT_AMOUNT),0) FROM CN_PAYMENT_TRANSACTIONS WHERE CREDITED_SALESREP_ID=WK.SALESREP_ID AND PAYRUN_ID

=WK.PAYRUN_ID

AND INCENTIVE_TYPE_CODE = 'MANUAL_PAY_ADJ' AND RECOVERABLE_FLAG )

='N'

+

(SELECT NVL(-1 *SUM(PAYMENT_AMOUNT),0) FROM CN_PAYMENT_TRANSACTIONS WHERE CREDITED_SALESREP_ID=WK.SALESREP_ID AND PAYRUN_ID

=WK.PAYRUN_ID

AND INCENTIVE_TYPE_CODE = 'PMTPLN_REC' AND WAIVE_FLAG

='Y'

) NREC_DRAW_PD, WK.PAYRUN_ID PAYRUN_ID , WK.SALESREP_ID SALESREP_ID, CS.name SALES_PERSON, CP.name PAYMENT_BATCH,

HOU.NAME OPERATING_UNIT, CS.SALESREP_ID CS_SALESREP_ID, CS.ORG_ID CS_ORG_ID, CP.name CP_NAME, CP.ORG_ID CP_ORG_ID, hou.ORGANIZATION_ID hou_ORGANIZATION_ID FROM CN_PAYMENT_WORKSHEETS WK, CN_SALESREPS CS, CN_PAYRUNS CP, HR_OPERATING_UNITS HOU WHERE WK.SALESREP_ID=CS.SALESREP_ID and WK.PAYRUN_ID =CP.PAYRUN_ID and CS.ORG_ID=HOU.ORGANIZATION_ID --AND WK.SALESREP_ID = 100000004 -- AND WK.PAYRUN_ID =2654 and WK.QUOTA_ID(+) is null )V ;

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_DAYS_SOVS_V" ("OPPORTUNITY_NAME", "OPPORTUNITY_NUMBER", "CREATION_DATE", "CREATED_BY_ID", "CREATED_BY", "CUSTOMER", "PARTY_TYPE", "CUSTOMER_ID", "PARTY_NUMBER", "STATUS", "SALES_CHANNEL", "SOURCE_PROMOTION_ID", "CYCLE", "CYCLE_DESCRIPTION", "TOTAL_AMOUNT", "CURRENCY_CODE", "DECISION_TIMEFRAME_CODE", "PRODUCT_CATEGORY_ID", "PRODUCT_CAT_SET_ID", "PRODUCT_CATEGORY", "PRODUCT", "PRODUCT_UOM", "QUANTITY", "PRODUCT_AMOUNT", "EMPLOYEES_TOTAL", "EQUAL_TO_0", "BETWEEN_1_9", "BETWEEN_10_29", "BETWEEN_30_59", "BETWEEN_60_89", "BETWEEN_90_179", "BETWEEN_180_365", "GREATER_THAN_365", "LEAD_ID", "FU_USER_ID", "PARTY_ID", "SALES_CHANNEL_CODE", "SALES_STAGE_ID", "LEAD_LINE_ID", "CATEGORY_ID", "INVENTORY_ITEM_ID", "ORGANIZATION_ID", "UOM_CODE", "ASSAT_LANGUAGE", "MUOMT_LANGUAGE", "COPYRIGHT") AS SELECT AL.DESCRIPTION OPPORTUNITY_NAME, AL.LEAD_NUMBER OPPORTUNITY_NUMBER, AL.CREATION_DATE, AL.CREATED_BY CREATED_BY_ID, FU.USER_NAME CREATED_BY, HP.PARTY_NAME CUSTOMER, HP.PARTY_TYPE, AL.CUSTOMER_ID, HP.PARTY_NUMBER, AL.STATUS STATUS, ASO_CHANNEL.SALES_CHANNEL SALES_CHANNEL, AL.SOURCE_PROMOTION_ID , ASSAT.NAME CYCLE, ASSAT.DESCRIPTION CYCLE_DESCRIPTION, AL.TOTAL_AMOUNT, AL.CURRENCY_CODE, AL.DECISION_TIMEFRAME_CODE, ALLA.PRODUCT_CATEGORY_ID, ALLA.PRODUCT_CAT_SET_ID, MC.DESCRIPTION PRODUCT_CATEGORY,

NVL(MSIK.DESCRIPTION,MC.DESCRIPTION) PRODUCT, MUOMT.UNIT_OF_MEASURE PRODUCT_UOM, ALLA.QUANTITY, ALLA.TOTAL_AMOUNT PRODUCT_AMOUNT, HP.EMPLOYEES_TOTAL, CASE WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) =0 THEN AL.TOTAL_AMOUNT END EQUAL_TO_0, CASE WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) BETWEEN 1 AND 9 THEN AL.TOTAL_AMOUNT END BETWEEN_1_9, CASE WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) BETWEEN 10 AND 29 THEN AL.TOTAL_AMOUNT END BETWEEN_10_29, CASE WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) BETWEEN 30 AND 59 THEN AL.TOTAL_AMOUNT END BETWEEN_30_59, CASE WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) BETWEEN 60 AND 89 THEN AL.TOTAL_AMOUNT END BETWEEN_60_89, CASE WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) BETWEEN 90 AND 179 THEN AL.TOTAL_AMOUNT END BETWEEN_90_179,

CASE WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) BETWEEN 180 AND 365 THEN AL.TOTAL_AMOUNT END BETWEEN_180_365, CASE WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) >365 THEN AL.TOTAL_AMOUNT END GREATER_THAN_365, -----PRIMARY KEYS AL.LEAD_ID LEAD_ID, FU.USER_ID FU_USER_ID, HP.PARTY_ID, ASO_CHANNEL.SALES_CHANNEL_CODE, ASSAT.SALES_STAGE_ID, ALLA.LEAD_LINE_ID , MC.CATEGORY_ID, MSIK.INVENTORY_ITEM_ID, MSIK.ORGANIZATION_ID, MUOMT.UOM_CODE, ASSAT.LANGUAGE ASSAT_LANGUAGE, MUOMT.LANGUAGE MUOMT_LANGUAGE , 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.' Copyright --DESCR#FLEXFIELD#START --DESCR#FLEXFIELD#END --GL#ACCOUNTFF#START --GL#ACCOUNTFF#END FROM AS_LEADS AL, FND_USER FU,

HZ_PARTIES HP, ASO_I_SALES_CHANNELS_V ASO_CHANNEL, AS_SALES_STAGES_ALL_TL ASSAT, AS_LEAD_LINES_ALL ALLA, MTL_CATEGORIES MC, MTL_SYSTEM_ITEMS_KFV MSIK, MTL_UNITS_OF_MEASURE_TL MUOMT WHERE AL.CREATED_BY

=FU.USER_ID

AND AL.CUSTOMER_ID

=HP.PARTY_ID

AND AL.CHANNEL_CODE

= ASO_CHANNEL.SALES_CHANNEL_CODE(+)

AND AL.SALES_STAGE_ID

=ASSAT.SALES_STAGE_ID(+)

AND ASSAT.LANGUAGE(+) AND AL.LEAD_ID

= USERENV('LANG')

=ALLA.LEAD_ID(+)

AND ALLA.PRODUCT_CATEGORY_ID=MC.CATEGORY_ID(+) AND ALLA.INVENTORY_ITEM_ID =MSIK.INVENTORY_ITEM_ID(+) AND ALLA.ORGANIZATION_ID =MSIK.ORGANIZATION_ID(+) AND ALLA.UOM_CODE

=MUOMT.UOM_CODE(+)

AND MUOMT.LANGUAGE(+)=USERENV('LANG') --AND AL.LEAD_ID NOT IN (SELECT OPPORTUNITY_ID FROM AS_SALES_LEAD_OPPORTUNITY) AND AL.STATUS

= 'WON'

AND AL.DESCRIPTION IS NOT NULL ORDER BY 3 ;

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_DAYS_WIN_V" ("OPPORTUNITY_NAME", "OPPORTUNITY_NUMBER", "CREATION_DATE", "CREATED_BY_ID", "CREATED_BY", "CUSTOMER", "PARTY_TYPE", "CUSTOMER_ID", "PARTY_NUMBER", "STATUS", "SALES_CHANNEL", "SOURCE_PROMOTION_ID", "CYCLE", "CYCLE_DESCRIPTION", "TOTAL_AMOUNT", "CURRENCY_CODE", "DECISION_TIMEFRAME_CODE", "PRODUCT_CATEGORY_ID", "PRODUCT_CAT_SET_ID", "PRODUCT_CATEGORY", "PRODUCT", "PRODUCT_UOM", "QUANTITY", "PRODUCT_AMOUNT", "EMPLOYEES_TOTAL", "EQUAL_TO_0", "BETWEEN_1_9", "BETWEEN_10_29", "BETWEEN_30_59", "BETWEEN_60_89", "BETWEEN_90_179", "BETWEEN_180_365", "GREATER_THAN_365", "LEAD_ID", "FU_USER_ID", "PARTY_ID", "SALES_CHANNEL_CODE", "SALES_STAGE_ID", "LEAD_LINE_ID", "CATEGORY_ID", "INVENTORY_ITEM_ID", "ORGANIZATION_ID", "UOM_CODE", "LANGUAGE", "MUOMT_LANGUAGE", "COPYRIGHT") AS SELECT AL.DESCRIPTION OPPORTUNITY_NAME, AL.LEAD_NUMBER OPPORTUNITY_NUMBER, AL.CREATION_DATE, AL.CREATED_BY CREATED_BY_ID, FU.USER_NAME CREATED_BY, HP.PARTY_NAME CUSTOMER, HP.PARTY_TYPE, AL.CUSTOMER_ID, HP.PARTY_NUMBER, AL.STATUS STATUS, ASO_CHANNEL.SALES_CHANNEL SALES_CHANNEL, AL.SOURCE_PROMOTION_ID , ASSAT.NAME CYCLE, ASSAT.DESCRIPTION CYCLE_DESCRIPTION, AL.TOTAL_AMOUNT, AL.CURRENCY_CODE, AL.DECISION_TIMEFRAME_CODE, ALLA.PRODUCT_CATEGORY_ID, ALLA.PRODUCT_CAT_SET_ID, MC.DESCRIPTION PRODUCT_CATEGORY,

NVL(MSIK.DESCRIPTION,MC.DESCRIPTION) PRODUCT, MUOMT.UNIT_OF_MEASURE PRODUCT_UOM, ALLA.QUANTITY, ALLA.TOTAL_AMOUNT PRODUCT_AMOUNT, HP.EMPLOYEES_TOTAL, CASE WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) =0 THEN 'WON' END EQUAL_TO_0, CASE WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) BETWEEN 1 AND 9 THEN 'WON' END BETWEEN_1_9, CASE WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) BETWEEN 10 AND 29 THEN 'WON' END BETWEEN_10_29, CASE WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) BETWEEN 30 AND 59 THEN 'WON' END BETWEEN_30_59, CASE WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) BETWEEN 60 AND 89 THEN 'WON' END BETWEEN_60_89, CASE WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) BETWEEN 90 AND 179 THEN 'WON' END BETWEEN_90_179,

CASE WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) BETWEEN 180 AND 365 THEN 'WON' END BETWEEN_180_365, CASE WHEN ROUND(AL.DECISION_DATE-AL.CREATION_DATE) >365 THEN 'WON' END GREATER_THAN_365, -----PRIMARY KEYS AL.LEAD_ID LEAD_ID, FU.USER_ID FU_USER_ID, HP.PARTY_ID, ASO_CHANNEL.SALES_CHANNEL_CODE, ASSAT.SALES_STAGE_ID, ALLA.LEAD_LINE_ID , MC.CATEGORY_ID, MSIK.INVENTORY_ITEM_ID, MSIK.ORGANIZATION_ID, MUOMT.UOM_CODE, ASSAT.LANGUAGE, MUOMT.LANGUAGE MUOMT_LANGUAGE , 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.' Copyright --DESCR#FLEXFIELD#START --DESCR#FLEXFIELD#END --GL#ACCOUNTFF#START --GL#ACCOUNTFF#END FROM AS_LEADS AL, FND_USER FU,

HZ_PARTIES HP, ASO_I_SALES_CHANNELS_V ASO_CHANNEL, AS_SALES_STAGES_ALL_TL ASSAT, AS_LEAD_LINES_ALL ALLA, MTL_CATEGORIES MC, MTL_SYSTEM_ITEMS_KFV MSIK, MTL_UNITS_OF_MEASURE_TL MUOMT WHERE AL.CREATED_BY

=FU.USER_ID

AND AL.CUSTOMER_ID

=HP.PARTY_ID

AND AL.CHANNEL_CODE

= ASO_CHANNEL.SALES_CHANNEL_CODE(+)

AND AL.SALES_STAGE_ID

=ASSAT.SALES_STAGE_ID(+)

AND ASSAT.LANGUAGE(+) AND AL.LEAD_ID

= USERENV('LANG')

=ALLA.LEAD_ID(+)

AND ALLA.PRODUCT_CATEGORY_ID=MC.CATEGORY_ID(+) AND ALLA.INVENTORY_ITEM_ID =MSIK.INVENTORY_ITEM_ID(+) AND ALLA.ORGANIZATION_ID =MSIK.ORGANIZATION_ID(+) AND ALLA.UOM_CODE AND MUOMT.LANGUAGE(+)

=MUOMT.UOM_CODE(+) = USERENV('LANG')

--AND AL.LEAD_ID NOT IN (SELECT OPPORTUNITY_ID FROM AS_SALES_LEAD_OPPORTUNITY) AND AL.STATUS

= 'WON'

AND AL.DESCRIPTION IS NOT NULL ORDER BY 3 ;

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_FORECAST_DETAILS_V" ("CATEGORY", "PERIOD", "PRODUCT_CATEGORY_ID", "FORECAST_CATEGORY_ID", "BEST_FORECAST_AMOUNT", "FORECAST_AMOUNT", "WORST_FORECAST_AMOUNT", "FORECAST_ID", "CURRENCY_CODE", "STATUS_CODE", "FORECAST_WORKSHEET_ID", "OPP_WORST_FORECAST_AMOUNT", "OPP_FORECAST_AMOUNT", "OPP_BEST_FORECAST_AMOUNT", "CREATION_DATE", "CREATED_BY_ID", "CREATED_BY", "PROD_WORKSHEET_LINE_ID", "FU_USER_ID", "COPYRIGHT") AS SELECT AFC.FORECAST_CATEGORY_NAME CATEGORY, APWL.PERIOD_NAME PERIOD, APWL.PRODUCT_CATEGORY_ID, apwl.forecast_category_id, APWL.BEST_FORECAST_AMOUNT, APWL.FORECAST_AMOUNT, APWL.WORST_FORECAST_AMOUNT, APWL.FORECAST_ID, APWL.CURRENCY_CODE, APWL.STATUS_CODE, APWL.FORECAST_WORKSHEET_ID, APWL.OPP_WORST_FORECAST_AMOUNT, APWL.OPP_FORECAST_AMOUNT, APWL.OPP_BEST_FORECAST_AMOUNT, APWL.CREATION_DATE, APWL.CREATED_BY CREATED_BY_ID , FU.USER_NAME CREATED_BY, -----Primary Keys APWL.PROD_WORKSHEET_LINE_ID, FU.USER_ID FU_USER_ID , 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.' Copyright --descr#flexfield#start

--descr#flexfield#end --gl#accountff#start --gl#accountff#end FROM AS_PROD_WORKSHEET_LINES APWL, AS_FORECAST_CATEGORIES_VL AFC, fnd_user fu WHERE APWL.FORECAST_CATEGORY_ID=AFC.FORECAST_CATEGORY_ID AND APWL.created_by ;

=fu.user_id

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_FORECAST_HISTORY_V" ("SUBMITTED_DATE", "FORECAST_ID", "CATEGORY", "PERIOD_NAME", "CURRENCY_CODE", "STATUS_CODE", "SAVE_DATE", "BEST_FORECAST_AMOUNT", "FORECAST_AMOUNT", "WORST_FORECAST_AMOUNT", "FORECAST_CATEGORY_ID", "CREATED_BY_ID", "CREATED_BY", "FORECAST_WORKSHEET_ID", "FU_USER_ID", "COPYRIGHT") AS SELECT APW.CREATION_DATE SUBMITTED_DATE, APW.FORECAST_ID, APW.WORKSHEET_TYPE CATEGORY, APW.PERIOD_NAME, APW.CURRENCY_CODE, APW.STATUS_CODE, APW.SAVE_DATE, APW.BEST_FORECAST_AMOUNT, APW.FORECAST_AMOUNT, APW.WORST_FORECAST_AMOUNT, APW.FORECAST_CATEGORY_ID, APW.CREATED_BY CREATED_BY_ID, FU.USER_NAME CREATED_BY, -----Primary Keys APW.FORECAST_WORKSHEET_ID, FU.USER_ID FU_USER_ID , 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.' Copyright --descr#flexfield#start --descr#flexfield#end --gl#accountff#start --gl#accountff#end FROM AS_FORECAST_WORKSHEETS APW, FND_USER FU

WHERE APW.CREATED_BY=FU.USER_ID ;

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_LEAD_OPPORTUNITIES_V" ("OPPORTUNITY_NUMBER", "OPPORTUNITY", "OPP_CREATION_DATE", "OPERATING_UNIT", "OPP_CREATED_BY", "OPP_CREATED_BY_ID", "OPP_CUSTOMER", "CLOSE_DATE", "OPP_STATUS", "OPP_STAGE", "OPP_SALES_CHANNEL", "OPP_AMOUNT", "OPP_CURRENCY_CODE", "WIN_PROBABILITY", "OPP_CLOSE_REASON", "SALES_METHODOLOGY", "SALES_METHODOLOGY_DESCRIPTION", "ADDRESS1", "ADDRESS2", "ADDRESS3", "ADDRESS4", "CITY", "POSTAL_CODE", "STATE", "COUNTRY", "LEAD_NAME", "LEAD_NUMBER", "CREATION_DATE", "CREATED_BY_ID", "CREATED_BY", "CUSTOMER", "PARTY_TYPE", "CUSTOMER_ID", "PARTY_NUMBER", "ASSIGN_TO_PERSON_ID", "ASSIGN_TO_SALESFORCE_ID", "ASSIGNED_NAME", "PRIMARY_CONTACT", "PHONE_NUM", "LEAD_STATUS", "SALES_CHANNEL", "SOURCE_PROMOTION_ID", "RANK", "BUDGET_AMOUNT", "BUDGET_STATUS_CODE", "BUDGET_STATUS", "CURRENCY_CODE", "DECISION_TIMEFRAME_CODE", "PARTY_SITE_ID", "OPPORTUNITY_ID", "LOCATION_ID", "SALES_METHODOLOGY_ID", "ORGANIZATION_ID", "LEAD_ID", "USER_ID", "PARTY_ID", "SALES_STAGE_ID", "ORG_ID", "LEAD_OPPORTUNITY_ID", "COPYRIGHT") AS SELECT AL.LEAD_NUMBER OPPORTUNITY_NUMBER, AL.DESCRIPTION OPPORTUNITY, AL.CREATION_DATE OPP_CREATION_DATE, OOD.ORGANIZATION_NAME OPERATING_UNIT, FU.USER_NAME OPP_CREATED_BY, FU.CREATED_BY opp_CREATED_BY_ID, HP.PARTY_NAME OPP_CUSTOMER, AL.DECISION_DATE CLOSE_DATE, AL.STATUS OPP_STATUS, ASSV.NAME OPP_STAGE, AL.CHANNEL_CODE OPP_SALES_CHANNEL, AL.TOTAL_AMOUNT OPP_AMOUNT, AL.CURRENCY_CODE OPP_CURRENCY_CODE, AL.WIN_PROBABILITY,

al.close_reason opp_close_reason, asml.name SALES_METHODOLOGY, asml.description SALES_METHODOLOGY_description, HL.ADDRESS1 , HL.ADDRESS2, HL.ADDRESS3, HL.ADDRESS4, HL.CITY, HL.POSTAL_CODE, HL.STATE, HL.COUNTRY, -- ARC_SOURCE_CODE_FOR , /* DECODE (amsc.arc_source_code_for , 'CAMP', (SELECT campaign_name FROM AMS_CAMPAIGNS_ALL_TL WHERE campaign_id = amsc.source_code_for_id ), 'EVEH', (SELECT event_header_name FROM AMS_EVENT_HEADERS_ALL_TL WHERE event_header_id = amsc.source_code_for_id ), 'EONE', (SELECT event_offer_name FROM AMS_EVENT_OFFERS_ALL_TL WHERE event_offer_id = amsc.source_code_for_id ), 'EVEO', (SELECT event_offer_name FROM AMS_EVENT_OFFERS_ALL_TL WHERE event_offer_id = amsc.source_code_for_id ), 'CSCH',

(SELECT schedule_name FROM ams_campaign_schedules_tl WHERE schedule_id = amsc.source_code_for_id ), 'OFFR', (SELECT description FROM qp_list_headers_tl WHERE LIST_HEADER_ID = AMSC.SOURCE_CODE_FOR_ID ), NULL ) SOURCE, */ ASLV.DESCRIPTION LEAD_NAME, ASLV.LEAD_NUMBER LEAD_NUMBER, ASLV.CREATION_DATE, ASLV.CREATED_BY CREATED_BY_ID, FU.USER_NAME CREATED_BY, ASLV.CUSTOMER_NAME CUSTOMER, ASLV.PARTY_TYPE, ASLV.CUSTOMER_ID, ASLV.PARTY_NUMBER, ASLV.ASSIGN_TO_PERSON_ID, ASLV.ASSIGN_TO_SALESFORCE_ID, ASLV.ASSIGNED_NAME, ASLV.PRIMARY_CONTACT_NAME PRIMARY_CONTACT, ASLV.PHONE_NUM, ASLV.LEAD_STATUS, ASLV.SALES_CHANNEL, ASLV.SOURCE_PROMOTION_ID , ASLV.RANK, ASLV.BUDGET_AMOUNT, ASLV.BUDGET_STATUS_CODE, ASLV.BUDGET_STATUS,

ASLV.CURRENCY_CODE, ASLV.DECISION_TIMEFRAME_CODE, --Primary Keys HPS.PARTY_SITE_ID, AL.LEAD_ID OPPORTUNITY_ID, HL.LOCATION_ID, asml.SALES_METHODOLOGY_ID, OOD.ORGANIZATION_ID, ASLV.SALES_LEAD_ID LEAD_ID, FU.USER_ID, HP.PARTY_ID, ASSV.SALES_STAGE_ID, ASSV.ORG_ID, -- AMSC.SOURCE_CODE_ID, ASLO.LEAD_OPPORTUNITY_ID , 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.' Copyright --descr#flexfield#start --descr#flexfield#end --gl#accountff#start --gl#accountff#end FROM AS_LEADS AL, ORG_ORGANIZATION_DEFINITIONS OOD, FND_USER FU, HZ_PARTIES HP, AS_SALES_STAGES_VL ASSV, HZ_PARTY_SITES HPS, HZ_LOCATIONS HL, AS_SALES_METHODOLOGY_VL ASML,

-- AMS_SOURCE_CODES AMSC, AS_SALES_LEAD_OPPORTUNITY ASLO, AS_SALES_LEADS_V ASLV WHERE 1

=1

AND AL.ORG_ID

=OOD.ORGANIZATION_ID

AND AL.CREATED_BY

=FU.USER_ID

AND AL.CUSTOMER_ID

=HP.PARTY_ID

AND ASSV.SALES_STAGE_ID =AL.SALES_STAGE_ID AND AL.ORG_ID

=ASSV.ORG_ID

AND HPS.LOCATION_ID

=HL.LOCATION_ID

AND HPS.PARTY_SITE_ID

=AL.ADDRESS_ID

AND AL.SALES_METHODOLOGY_ID=ASML.SALES_METHODOLOGY_ID -- AND AMSC.SOURCE_CODE_ID =AL.SOURCE_PROMOTION_ID AND AL.LEAD_ID

=ASLO.OPPORTUNITY_ID

AND ASLV.sales_lead_id ;

=aslo.sales_lead_id

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_LEAD_TASKS_V" ("LEAD_NAME", "LEAD_NUMBER", "CREATION_DATE", "CREATED_BY_ID", "CREATED_BY", "CUSTOMER", "PARTY_TYPE", "CUSTOMER_ID", "PARTY_NUMBER", "ASSIGN_TO_PERSON_ID", "ASSIGN_TO_SALESFORCE_ID", "ASSIGNED_NAME", "PRIMARY_CONTACT", "PHONE_NUM", "LEAD_STATUS", "SALES_CHANNEL", "SOURCE", "SOURCE_PROMOTION_ID", "RANK", "BUDGET_AMOUNT", "BUDGET_STATUS_CODE", "BUDGET_STATUS", "CURRENCY_CODE", "DECISION_TIMEFRAME_CODE", "TASK_NUMBER", "SUBJECT", "TYPE", "TASK_TYPE_ID", "TASK_STATUS", "TASK_STATUS_ID", "DUE_DATE", "OWNER_ID", "OWNER", "OWNER_TYPE_CODE", "OWNER_TYPE", "CUSTOMER_NUMBER", "LEAD_ID", "TASK_ID", "FU_USER_ID", "COPYRIGHT") AS SELECT ASLV.DESCRIPTION LEAD_NAME, ASLV.LEAD_NUMBER LEAD_NUMBER, ASLV.CREATION_DATE, ASLV.CREATED_BY CREATED_BY_ID, FU.USER_NAME CREATED_BY, ASLV.CUSTOMER_NAME CUSTOMER, ASLV.PARTY_TYPE, ASLV.CUSTOMER_ID, ASLV.PARTY_NUMBER, ASLV.ASSIGN_TO_PERSON_ID, ASLV.ASSIGN_TO_SALESFORCE_ID, ASLV.ASSIGNED_NAME, ASLV.PRIMARY_CONTACT_NAME PRIMARY_CONTACT, ASLV.PHONE_NUM, ASLV.LEAD_STATUS, ASLV.SALES_CHANNEL, ASLV.SOURCE_PROMOTION_NAME SOURCE, ASLV.SOURCE_PROMOTION_ID , ASLV.RANK, ASLV.BUDGET_AMOUNT, ASLV.BUDGET_STATUS_CODE, ASLV.BUDGET_STATUS,

ASLV.CURRENCY_CODE, ASLV.DECISION_TIMEFRAME_CODE, JTV.TASK_NUMBER, JTV.TASK_NAME SUBJECT, JTV.TASK_TYPE TYPE, JTV.TASK_TYPE_ID, JTV.TASK_STATUS TASK_STATUS, JTV.TASK_STATUS_ID, JTV.SCHEDULED_END_DATE DUE_DATE, JTV.OWNER_ID, JTV.OWNER, JTV.OWNER_TYPE_CODE, JTV.OWNER_TYPE, JTV.CUSTOMER_NUMBER, -----Primary Keys ASLV.SALES_LEAD_ID LEAD_ID, JTV.TASK_ID, FU.USER_ID FU_USER_ID , 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.' Copyright --descr#flexfield#start --descr#flexfield#end --gl#accountff#start --gl#accountff#end FROM AS_SALES_LEADS_V ASLV, FND_USER FU, JTF_TASKS_V JTV WHERE ASLV.CREATED_BY =FU.USER_ID AND ASLV.SALES_LEAD_ID=JTV.SOURCE_OBJECT_ID

;

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_LEADS_V" ("LEAD_NAME", "LEAD_NUMBER", "CREATION_DATE", "CREATED_BY_ID", "CREATED_BY", "CUSTOMER", "PARTY_TYPE", "CUSTOMER_ID", "PARTY_NUMBER", "ASSIGN_TO_PERSON_ID", "ASSIGN_TO_SALESFORCE_ID", "ASSIGNED_NAME", "PRIMARY_CONTACT", "PHONE_NUM", "LEAD_STATUS", "SALES_CHANNEL", "SOURCE", "SOURCE_PROMOTION_ID", "RANK", "BUDGET_AMOUNT", "BUDGET_STATUS_CODE", "BUDGET_STATUS", "CURRENCY_CODE", "DECISION_TIMEFRAME_CODE", "LEAD_ID", "FU_USER_ID", "COPYRIGHT") AS SELECT ASLV.DESCRIPTION LEAD_NAME, ASLV.LEAD_NUMBER LEAD_NUMBER, ASLV.CREATION_DATE, ASLV.CREATED_BY CREATED_BY_ID, FU.USER_NAME CREATED_BY, ASLV.CUSTOMER_NAME CUSTOMER, ASLV.PARTY_TYPE, ASLV.CUSTOMER_ID, ASLV.PARTY_NUMBER, ASLV.ASSIGN_TO_PERSON_ID, ASLV.ASSIGN_TO_SALESFORCE_ID, ASLV.ASSIGNED_NAME, ASLV.PRIMARY_CONTACT_NAME PRIMARY_CONTACT, ASLV.PHONE_NUM, ASLV.LEAD_STATUS, ASLV.SALES_CHANNEL, ASLV.SOURCE_PROMOTION_NAME SOURCE, ASLV.SOURCE_PROMOTION_ID , ASLV.RANK, ASLV.BUDGET_AMOUNT, ASLV.BUDGET_STATUS_CODE, ASLV.BUDGET_STATUS,

ASLV.CURRENCY_CODE, ASLV.DECISION_TIMEFRAME_CODE, -----Primary Keys ASLV.SALES_LEAD_ID LEAD_ID, FU.USER_ID FU_USER_ID , 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.' Copyright --descr#flexfield#start --descr#flexfield#end --gl#accountff#start --gl#accountff#end FROM AS_SALES_LEADS_V ASLV, FND_USER FU WHERE ASLV.CREATED_BY=FU.USER_ID ;

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_OPP_QUT_SALEORDER_V" ("OPPORTUNITY_NUMBER", "OPPORTUNITY_NAME", "QUOTE_NAME", "SALES_ORDER_NUMBER", "REFERENCE_NUMBER", "ORDERED_DATE", "BOOKED_DATE", "ORDER_STATUS", "ORG_ID", "OPERATING_UNIT", "QUOTE_SOURCE_CODE", "CUSTOMER", "CONTACT", "CHANNEL_CODE", "AMOUNT", "EXPIRATION_DATE", "QUOTE_HEADER_ID", "ORDER_ID", "SHIP_TO_LOCATION", "SHIP_TO_ADDRESS1", "SHIP_TO_ADDRESS2", "SHIP_TO_ADDRESS3", "SHIP_TO_ADDRESS4", "SHIP_TO_ADDRESS5", "SHIP_TO_CITY", "SHIP_TO_COUNTRY", "SHIP_TO_STATE", "SHIP_TO_POSTAL_CODE", "BILL_TO_LOCATION", "BILL_TO_ADDRESS1", "BILL_TO_ADDRESS2", "BILL_TO_ADDRESS3", "BILL_TO_ADDRESS4", "BILL_TO_ADDRESS5", "BILL_TO_COUNTRY", "BILL_TO_STATE", "BILL_TO_POSTAL_CODE", "SALES_PERSON_NAME", "SHIPPING_METHOD_CODE", "FOB_POINT_CODE", "FREIGHT_TERMS_CODE", "FREIGHT_CARRIER_CODE", "PACKING_INSTRUCTIONS", "PURCHASE_ORDER_NUMBER", "PART_NUMBER", "REQUEST_DATE", "UOM", "SHIPPED", "ORDERED_QUANTITY", "STATUS", "PRICE", "TAX", "LEAD_ID", "QUOTE_NUMBER", "SALES_LEAD_ID", "RELATED_OBJECT_ID", "QUOTE_VERSION", "LEAD_OPPORTUNITY_ID", "LEAD_CONTACT_ID", "HEADER_ID", "LINE_ID", "SITE_USE_ID", "HZCS_BILL_TO_SITE_USE_ID", "CUST_ACCT_SITE_ID", "HCAS_BILL_TO_CUST_ACCT_SITE_ID", "PARTY_SITE_ID", "HZPS_BILL_TO_PARTY_SITE_ID", "LOCATION_ID", "HZL_BILL_TO_LOCATION_ID", "SALESREP_ID", "REP_ORG_ID", "ORGANIZATION_ID", "PARTY_ID", "COPYRIGHT") AS SELECT AL.LEAD_NUMBER OPPORTUNITY_NUMBER, AL.DESCRIPTION OPPORTUNITY_NAME, AQH.QUOTE_NAME QUOTE_NAME, OOH.ORDER_NUMBER SALES_ORDER_NUMBER, OOH.SOURCE_DOCUMENT_ID REFERENCE_NUMBER, OOH.ORDERED_DATE ORDERED_DATE, OOH.BOOKED_DATE BOOKED_DATE, OOH.FLOW_STATUS_CODE ORDER_STATUS, AQH.ORG_ID ORG_ID, HOU.NAME OPERATING_UNIT, AQH.QUOTE_SOURCE_CODE QUOTE_SOURCE_CODE, HZ.PARTY_NAME CUSTOMER , (SELECT NVL(HPP.PERSON_NAME,NULL) FROM HZ_ORG_CONTACTS_CPUI_V HOC, HZ_PERSON_PROFILES_CPUI_V HPP WHERE ASLC.CONTACT_PARTY_ID = HOC.RELATIONSHIP_PARTY_ID

AND HOC.OBJECT_ID

= ASLC.CUSTOMER_ID

AND HOC.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND HOC.SUBJECT_ID

= HPP.PARTY_ID

AND HOC.SUBJECT_TABLE_NAME = 'HZ_PARTIES' )CONTACT, AQH.SALES_CHANNEL_CODE CHANNEL_CODE, AQH.TOTAL_QUOTE_PRICE AMOUNT, AQH.QUOTE_EXPIRATION_DATE EXPIRATION_DATE, AQH.QUOTE_HEADER_ID , AQH.ORDER_ID, HZCS_SHIP_TO.LOCATION SHIP_TO_LOCATION, HZL_SHIP_TO.ADDRESS1 SHIP_TO_ADDRESS1, HZL_SHIP_TO.ADDRESS2 SHIP_TO_ADDRESS2, HZL_SHIP_TO.ADDRESS3 SHIP_TO_ADDRESS3, HZL_SHIP_TO.ADDRESS4 SHIP_TO_ADDRESS4, DECODE (HZL_SHIP_TO.CITY, NULL, NULL, HZL_SHIP_TO.CITY || ', ' ) || DECODE (HZL_SHIP_TO.STATE, NULL, HZL_SHIP_TO.PROVINCE || ', ', HZL_SHIP_TO.STATE || ', ' ) || DECODE (HZL_SHIP_TO.POSTAL_CODE, NULL, NULL, HZL_SHIP_TO.POSTAL_CODE || ', ' ) || DECODE (HZL_SHIP_TO.COUNTRY, NULL, NULL, HZL_SHIP_TO.COUNTRY) SHIP_TO_ADDRESS5, HZL_SHIP_TO.CITY SHIP_TO_CITY, HZL_SHIP_TO.COUNTRY SHIP_TO_COUNTRY, HZL_SHIP_TO.STATE SHIP_TO_STATE, HZL_SHIP_TO.POSTAL_CODE SHIP_TO_POSTAL_CODE, HZCS_BILL_TO.LOCATION BILL_TO_LOCATION, HZL_BILL_TO.ADDRESS1 BILL_TO_ADDRESS1,

HZL_BILL_TO.ADDRESS2 BILL_TO_ADDRESS2, HZL_BILL_TO.ADDRESS3 BILL_TO_ADDRESS3, HZL_BILL_TO.ADDRESS4 BILL_TO_ADDRESS4, DECODE (HZL_BILL_TO.CITY, NULL, NULL, HZL_BILL_TO.CITY || ', ' ) || DECODE (HZL_BILL_TO.STATE, NULL, HZL_BILL_TO.PROVINCE || ', ', HZL_BILL_TO.STATE || ', ' ) || DECODE (HZL_BILL_TO.POSTAL_CODE, NULL, NULL, HZL_BILL_TO.POSTAL_CODE || ', ' ) BILL_TO_ADDRESS5, HZL_BILL_TO.COUNTRY BILL_TO_COUNTRY, HZL_BILL_TO.STATE BILL_TO_STATE, HZL_BILL_TO.POSTAL_CODE BILL_TO_POSTAL_CODE, REP.NAME SALES_PERSON_NAME, OOH.SHIPPING_METHOD_CODE SHIPPING_METHOD_CODE, OOH.FOB_POINT_CODE FOB_POINT_CODE, OOH.FREIGHT_TERMS_CODE FREIGHT_TERMS_CODE, OOH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER_CODE, OOH.PACKING_INSTRUCTIONS PACKING_INSTRUCTIONS, OOH.CUST_PO_NUMBER PURCHASE_ORDER_NUMBER, OOL.ORDERED_ITEM PART_NUMBER, OOL.REQUEST_DATE REQUEST_DATE, OOL.ORDER_QUANTITY_UOM UOM, OOL.SHIPPED_QUANTITY SHIPPED, OOL.ORDERED_QUANTITY ORDERED_QUANTITY, OOL.FLOW_STATUS_CODE STATUS, OOL.UNIT_SELLING_PRICE PRICE, OOL.TAX_VALUE TAX, --PRIMARY KEYS

AL.LEAD_ID, AQH.QUOTE_NUMBER QUOTE_NUMBER, ASL.SALES_LEAD_ID , AQRO.RELATED_OBJECT_ID, AQH.QUOTE_VERSION, ASLP.LEAD_OPPORTUNITY_ID, ASLC.LEAD_CONTACT_ID, OOH.HEADER_ID, OOL.LINE_ID, HZCS_SHIP_TO.SITE_USE_ID, HZCS_BILL_TO.SITE_USE_ID HZCS_BILL_TO_SITE_USE_ID, HCAS_SHIP_TO.CUST_ACCT_SITE_ID, HCAS_BILL_TO.CUST_ACCT_SITE_ID HCAS_BILL_TO_CUST_ACCT_SITE_ID, HZPS_SHIP_TO.PARTY_SITE_ID, HZPS_BILL_TO.PARTY_SITE_ID HZPS_BILL_TO_PARTY_SITE_ID, HZL_SHIP_TO.LOCATION_ID, HZL_BILL_TO.LOCATION_ID HZL_BILL_TO_LOCATION_ID, REP.SALESREP_ID, REP.ORG_ID REP_ORG_ID, HOU.ORGANIZATION_ID, HZ.PARTY_ID , 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.' Copyright --descr#flexfield#start --descr#flexfield#end --gl#accountff#start --gl#accountff#end FROM AS_LEADS AL, ASO_QUOTE_RELATED_OBJECTS AQRO,

ASO_QUOTE_HEADERS AQH, HZ_PARTIES HZ, AS_SALES_LEADS ASL, AS_SALES_LEAD_OPPORTUNITY ASLP, AS_SALES_LEAD_CONTACTS ASLC, OE_ORDER_HEADERS OOH, OE_ORDER_LINES OOL, HZ_CUST_SITE_USES HZCS_SHIP_TO, HZ_CUST_ACCT_SITES HCAS_SHIP_TO, HZ_PARTY_SITES HZPS_SHIP_TO, HZ_LOCATIONS HZL_SHIP_TO, HZ_CUST_SITE_USES HZCS_BILL_TO, HZ_CUST_ACCT_SITES HCAS_BILL_TO, HZ_PARTY_SITES HZPS_BILL_TO, HZ_LOCATIONS HZL_BILL_TO, RA_SALESREPS REP, HR_OPERATING_UNITS HOU WHERE AQRO.QUOTE_OBJECT_TYPE_CODE = 'HEADER' AND AQRO.RELATIONSHIP_TYPE_CODE = 'OPP_QUOTE' AND AL.LEAD_ID

= AQRO.OBJECT_ID

AND AQH.QUOTE_HEADER_ID AND AL.LEAD_ID

= AQRO.QUOTE_OBJECT_ID

= ASLP.OPPORTUNITY_ID

AND ASLP.SALES_LEAD_ID AND HZ.PARTY_ID

= ASL.SALES_LEAD_ID = ASL.CUSTOMER_ID

AND ASL.SALES_LEAD_ID

= ASLC.SALES_LEAD_ID(+)

AND OOH.HEADER_ID

= AQH.ORDER_ID

AND OOH.HEADER_ID

= OOL.HEADER_ID

--## Ship To Joins##-AND OOH.SHIP_TO_ORG_ID

= HZCS_SHIP_TO.SITE_USE_ID(+)

AND HZCS_SHIP_TO.CUST_ACCT_SITE_ID = HCAS_SHIP_TO.CUST_ACCT_SITE_ID(+) AND HCAS_SHIP_TO.PARTY_SITE_ID

= HZPS_SHIP_TO.PARTY_SITE_ID(+)

AND HZL_SHIP_TO.LOCATION_ID(+)

= HZPS_SHIP_TO.LOCATION_ID

--## Bill To Joins ##-AND OOH.INVOICE_TO_ORG_ID

= HZCS_BILL_TO.SITE_USE_ID(+)

AND HZCS_BILL_TO.CUST_ACCT_SITE_ID = HCAS_BILL_TO.CUST_ACCT_SITE_ID(+) AND HCAS_BILL_TO.PARTY_SITE_ID

= HZPS_BILL_TO.PARTY_SITE_ID(+)

AND HZL_BILL_TO.LOCATION_ID(+)

= HZPS_BILL_TO.LOCATION_ID

AND OOH.SALESREP_ID AND OOH.ORG_ID ;

= REP.SALESREP_ID(+) = HOU.ORGANIZATION_ID

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_OPPITEM_CHANNEL_SELL_V" ("OPPERTUNITY_NUMBER", "OPPERTUNITY_NAME", "CUSTOMER", "ADDRESS", "CLOSED_DATE", "METHODLOGY", "SATGE", "STAUS", "CLOSE_REASON", "WIN_PROBABILITY", "SALES_CHANNEL", "AMOUNT", "CURRENCY", "OPERATING_UNIT", "PRODUCT", "ITEM_NAME", "QUANTITY", "UOM", "TOTAL_AMOUNT", "FORECAST_DATE", "WORST", "FORECAST", "BEST", "REVENUE_AMOUNT", "REVENUE_PERCENT", "LEAD_ID", "PARTY_ID", "AL_SALES_METHODOLOGY_ID", "SALES_METHODOLOGY_ID", "SALES_STAGE_ID", "PARTY_SITE_ID", "LOCATION_ID", "SALES_CREDIT_ID", "LEAD_LINE_ID", "INVENTORY_ITEM_ID", "ORGANIZATION_ID", "MSKFV_INVENTORY_ITEM_ID", "MSKFV_ORGANIZATION_ID", "HOU_ORGANIZATION_ID", "COPYRIGHT") AS SELECT AL.LEAD_NUMBER OPPERTUNITY_NUMBER , AL.DESCRIPTION OPPERTUNITY_NAME , HP.PARTY_NAME CUSTOMER , HL.ADDRESS1 ||HL.ADDRESS2 ||HL.ADDRESS3 ||HL.ADDRESS4 ||HL.CITY ||HL.COUNTY ||HL.STATE ||HL.COUNTRY ADDRESS , AL.DECISION_DATE CLOSED_DATE , ASMV.NAME METHODLOGY , ASSA.NAME SATGE , AL.STATUS STAUS , AL.CLOSE_REASON CLOSE_REASON , AL.WIN_PROBABILITY WIN_PROBABILITY , AL.CHANNEL_CODE SALES_CHANNEL , AL.TOTAL_AMOUNT AMOUNT , FCV.NAME CURRENCY , -- AMSC.ARC_SOURCE_CODE_FOR SOURCE_NAME ,

HOU.NAME OPERATING_UNIT /* , DECODE (amsc.arc_source_code_for , 'CAMP', ( SELECT campaign_name FROM AMS_CAMPAIGNS_ALL_TL WHERE campaign_id = amsc.source_code_for_id ), 'EVEH', ( SELECT event_header_name FROM AMS_EVENT_HEADERS_ALL_TL WHERE event_header_id = amsc.source_code_for_id ), 'EONE', ( SELECT event_offer_name FROM AMS_EVENT_OFFERS_ALL_TL WHERE event_offer_id = amsc.source_code_for_id ), 'EVEO', ( SELECT event_offer_name FROM AMS_EVENT_OFFERS_ALL_TL WHERE event_offer_id = amsc.source_code_for_id ), 'CSCH', ( SELECT schedule_name FROM ams_campaign_schedules_tl WHERE schedule_id = amsc.source_code_for_id ), 'OFFR',

( SELECT description FROM qp_list_headers_tl WHERE list_header_id = amsc.source_code_for_id ), NULL ) source_name*/ , NVL(MSIT.DESCRIPTION,MCT.DESCRIPTION) PRODUCT , MSKFV.CONCATENATED_SEGMENTS ITEM_NAME , ASLL.QUANTITY QUANTITY , ASLL.UOM_CODE UOM , ASLL.TOTAL_AMOUNT TOTAL_AMOUNT , ASLL.FORECAST_DATE FORECAST_DATE , ASSC.OPP_WORST_FORECAST_AMOUNT WORST , ASSC.OPP_FORECAST_AMOUNT FORECAST , ASSC.OPP_BEST_FORECAST_AMOUNT BEST , ASSC.REVENUE_AMOUNT REVENUE_AMOUNT , ASSC.REVENUE_PERCENT REVENUE_PERCENT --Primary Keys , AL.LEAD_ID LEAD_ID , HP.PARTY_ID PARTY_ID , -- AMSC.SOURCE_CODE_ID SOURCE_CODE_ID , AL.SALES_METHODOLOGY_ID AL_SALES_METHODOLOGY_ID , ASMV.SALES_METHODOLOGY_ID SALES_METHODOLOGY_ID , ASSA.SALES_STAGE_ID SALES_STAGE_ID , HPS.PARTY_SITE_ID PARTY_SITE_ID , HL.LOCATION_ID LOCATION_ID , ASSC.SALES_CREDIT_ID SALES_CREDIT_ID,

ASLL.LEAD_LINE_ID, MSIT.INVENTORY_ITEM_ID, MSIT.ORGANIZATION_ID, MSKFV.INVENTORY_ITEM_ID MSKFV_INVENTORY_ITEM_ID, MSKFV.ORGANIZATION_ID MSKFV_ORGANIZATION_ID, HOU.ORGANIZATION_ID HOU_ORGANIZATION_ID , 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.' Copyright --descr#flexfield#start --descr#flexfield#end --gl#accountff#start --gl#accountff#end FROM AS_LEADS AL, AS_LEAD_LINES ASLL, MTL_SYSTEM_ITEMS_VL MSIT, MTL_CATEGORIES_TL MCT, MTL_SYSTEM_ITEMS_B_KFV MSKFV, HZ_PARTIES HP, HZ_PARTY_SITES HPS, HZ_LOCATIONS HL, -- AMS_SOURCE_CODES AMSC, AS_SALES_METHODOLOGY_VL ASMV, AS_SALES_STAGES_ALL_TL ASSA, FND_CURRENCIES_VL FCV, AS_SALES_CREDITS ASSC, HR_OPERATING_UNITS HOU WHERE AL.LEAD_ID

= ASLL.LEAD_ID

AND ASLL.INVENTORY_ITEM_ID AND ASLL.ORGANIZATION_ID

= MSIT.INVENTORY_ITEM_ID(+) = MSIT.ORGANIZATION_ID (+)

AND ASLL.PRODUCT_CATEGORY_ID

= MCT.CATEGORY_ID

AND MSKFV.INVENTORY_ITEM_ID (+) = MSIT.INVENTORY_ITEM_ID AND MSKFV.ORGANIZATION_ID (+) = MSIT.ORGANIZATION_ID AND ASLL.LEAD_LINE_ID AND HP.PARTY_ID AND AL.ADDRESS_ID

= ASSC.LEAD_LINE_ID = AL.CUSTOMER_ID = HPS.PARTY_SITE_ID(+)

AND AL.CUSTOMER_ID

= HPS.PARTY_ID(+)

AND HPS.LOCATION_ID

= HL.LOCATION_ID(+)

-- AND AL.SOURCE_PROMOTION_ID

= AMSC.SOURCE_CODE_ID(+)

AND ASMV.SALES_METHODOLOGY_ID(+) = AL.SALES_METHODOLOGY_ID AND ASSA.SALES_STAGE_ID (+) AND AL.CURRENCY_CODE AND AL.ORG_ID ;

= AL.SALES_STAGE_ID = FCV.CURRENCY_CODE

=HOU.ORGANIZATION_ID

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_OPPORTUNITY_TASKS_V" ("OPPORTUNITY_NUMBER", "OPPORTUNITY", "CREATION_DATE", "OPERATING_UNIT", "CREATED_BY", "CREATED_BY_ID", "CUSTOMER", "CLOSE_DATE", "STATUS", "STAGE", "SALES_CHANNEL", "AMOUNT", "CURRENCY_CODE", "WIN_PROBABILITY", "CLOSE_REASON", "SALES_METHODOLOGY", "SALES_METHODOLOGY_DESCRIPTION", "ADDRESS1", "ADDRESS2", "ADDRESS3", "ADDRESS4", "CITY", "POSTAL_CODE", "STATE", "COUNTRY", "TASK_ID", "TASK_NUMBER", "SUBJECT", "TYPE", "TASK_TYPE_ID", "TAST_STATUS", "TASK_STATUS_ID", "DUE_DATE", "OWNER_ID", "OWNER", "OWNER_TYPE_CODE", "OWNER_TYPE", "CUSTOMER_ID", "CUSTOMER_NAME", "CUSTOMER_NUMBER", "PARTY_SITE_ID", "LOCATION_ID", "SALES_METHODOLOGY_ID", "AMSC_SOURCE_CODE_ID", "SALES_STAGE_ID", "ORG_ID", "PARTY_ID", "ORGANIZATION_ID", "OPPORTUNITY_ID", "FU_USER_ID", "COPYRIGHT") AS SELECT AL.LEAD_NUMBER OPPORTUNITY_NUMBER, AL.DESCRIPTION OPPORTUNITY, AL.CREATION_DATE, OOD.ORGANIZATION_NAME OPERATING_UNIT, FU.USER_NAME CREATED_BY, FU.CREATED_BY CREATED_BY_ID, HP.PARTY_NAME CUSTOMER, AL.DECISION_DATE CLOSE_DATE, AL.STATUS STATUS, ASSV.NAME STAGE, AL.CHANNEL_CODE SALES_CHANNEL, AL.TOTAL_AMOUNT AMOUNT, AL.CURRENCY_CODE, al.win_probability, al.close_reason, asml.name SALES_METHODOLOGY, asml.description SALES_METHODOLOGY_description, HL.ADDRESS1 , HL.ADDRESS2, HL.ADDRESS3,

HL.ADDRESS4, HL.CITY, HL.POSTAL_CODE, HL.STATE, HL.COUNTRY, -- ARC_SOURCE_CODE_FOR , /*, DECODE (amsc.arc_source_code_for , 'CAMP', (SELECT campaign_name FROM AMS_CAMPAIGNS_ALL_TL WHERE campaign_id = amsc.source_code_for_id ), 'EVEH', (SELECT event_header_name FROM AMS_EVENT_HEADERS_ALL_TL WHERE event_header_id = amsc.source_code_for_id ), 'EONE', (SELECT event_offer_name FROM AMS_EVENT_OFFERS_ALL_TL WHERE event_offer_id = amsc.source_code_for_id ), 'EVEO', (SELECT event_offer_name FROM AMS_EVENT_OFFERS_ALL_TL WHERE event_offer_id = amsc.source_code_for_id ), 'CSCH', (SELECT schedule_name FROM ams_campaign_schedules_tl WHERE schedule_id = amsc.source_code_for_id ), 'OFFR', (SELECT description

FROM qp_list_headers_tl WHERE LIST_HEADER_ID = AMSC.SOURCE_CODE_FOR_ID ), NULL ) source, */ JTV.TASK_ID, JTV.TASK_NUMBER, JTV.TASK_NAME SUBJECT, JTV.TASK_TYPE TYPE, JTV.TASK_TYPE_ID, JTV.TASK_STATUS TAST_STATUS, JTV.TASK_STATUS_ID, JTV.SCHEDULED_END_DATE DUE_DATE, JTV.OWNER_ID, JTV.OWNER, JTV.OWNER_TYPE_CODE, JTV.OWNER_TYPE, JTV.CUSTOMER_ID, JTV.CUSTOMER_NAME, JTV.CUSTOMER_NUMBER, -----Primary Keys HPS.PARTY_SITE_ID, HL.LOCATION_ID, asml.SALES_METHODOLOGY_ID, AMSC.SOURCE_CODE_ID AMSC_SOURCE_CODE_ID, ASSV.SALES_STAGE_ID, ASSV.ORG_ID, HP.PARTY_ID, OOD.ORGANIZATION_ID, AL.LEAD_ID OPPORTUNITY_ID, FU.USER_ID FU_USER_ID

, 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.' Copyright --descr#flexfield#start --descr#flexfield#end --gl#accountff#start --gl#accountff#end FROM AS_LEADS AL, ORG_ORGANIZATION_DEFINITIONS OOD, FND_USER FU, HZ_PARTIES HP, AS_SALES_STAGES_VL ASSV, HZ_PARTY_SITES HPS, HZ_LOCATIONS HL, AS_SALES_METHODOLOGY_VL ASML, AMS_SOURCE_CODES AMSC, JTF_TASKS_V JTV WHERE 1

=1

AND AL.ORG_ID

=OOD.ORGANIZATION_ID

AND AL.CREATED_BY

=FU.USER_ID

AND AL.CUSTOMER_ID

=HP.PARTY_ID

AND ASSV.SALES_STAGE_ID =AL.SALES_STAGE_ID AND AL.ORG_ID

=ASSV.ORG_ID

AND HPS.LOCATION_ID

=HL.LOCATION_ID

AND HPS.PARTY_SITE_ID

=AL.ADDRESS_ID

AND AL.SALES_METHODOLOGY_ID=ASML.SALES_METHODOLOGY_ID AND AMSC.SOURCE_CODE_ID =AL.SOURCE_PROMOTION_ID AND AL.LEAD_ID ;

=JTV.SOURCE_OBJECT_ID

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_OPPORTUNITY_V" ("OPPORTUNITY_NUMBER", "OPPORTUNITY", "CREATION_DATE", "OPERATING_UNIT", "CREATED_BY", "CREATED_BY_ID", "CUSTOMER", "CLOSE_DATE", "STATUS", "STAGE", "SALES_CHANNEL", "AMOUNT", "CURRENCY_CODE", "WIN_PROBABILITY", "CLOSE_REASON", "SALES_METHODOLOGY", "SALES_METHODOLOGY_DESCRIPTION", "ADDRESS1", "ADDRESS2", "ADDRESS3", "ADDRESS4", "CITY", "POSTAL_CODE", "STATE", "COUNTRY", "PARTY_ID", "OPPORTUNITY_ID", "PARTY_SITE_ID", "LOCATION_ID", "SALES_METHODOLOGY_ID", "SALES_STAGE_ID", "ORG_ID", "ORGANIZATION_ID", "FU_USER_ID", "COPYRIGHT") AS SELECT AL.LEAD_NUMBER OPPORTUNITY_NUMBER, AL.DESCRIPTION OPPORTUNITY, AL.CREATION_DATE, OOD.ORGANIZATION_NAME OPERATING_UNIT, FU.USER_NAME CREATED_BY, FU.CREATED_BY CREATED_BY_ID, HP.PARTY_NAME CUSTOMER, AL.DECISION_DATE CLOSE_DATE, AL.STATUS STATUS, ASSV.NAME STAGE, AL.CHANNEL_CODE SALES_CHANNEL, AL.TOTAL_AMOUNT AMOUNT, AL.CURRENCY_CODE, al.win_probability, al.close_reason, asml.name SALES_METHODOLOGY, asml.description SALES_METHODOLOGY_description, HL.ADDRESS1 , HL.ADDRESS2, HL.ADDRESS3, HL.ADDRESS4, HL.CITY,

HL.POSTAL_CODE, HL.STATE, HL.COUNTRY, -- arc_source_code_for, /*, DECODE (amsc.arc_source_code_for , 'CAMP', (SELECT campaign_name FROM AMS_CAMPAIGNS_ALL_TL WHERE campaign_id = amsc.source_code_for_id ), 'EVEH', (SELECT event_header_name FROM AMS_EVENT_HEADERS_ALL_TL WHERE event_header_id = amsc.source_code_for_id ), 'EONE', (SELECT event_offer_name FROM AMS_EVENT_OFFERS_ALL_TL WHERE event_offer_id = amsc.source_code_for_id ), 'EVEO', (SELECT event_offer_name FROM AMS_EVENT_OFFERS_ALL_TL WHERE event_offer_id = amsc.source_code_for_id ), 'CSCH', (SELECT schedule_name FROM ams_campaign_schedules_tl WHERE schedule_id = amsc.source_code_for_id ), 'OFFR', (SELECT description FROM qp_list_headers_tl WHERE LIST_HEADER_ID = AMSC.SOURCE_CODE_FOR_ID

), NULL ) source */ --Primary Keys HP.PARTY_ID, AL.LEAD_ID OPPORTUNITY_ID, HPS.PARTY_SITE_ID, HL.LOCATION_ID, asml.SALES_METHODOLOGY_ID, ASSV.SALES_STAGE_ID, ASSV.ORG_ID, OOD.ORGANIZATION_ID, FU.USER_ID FU_USER_ID , 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.' Copyright -- AMSC.SOURCE_CODE_ID --descr#flexfield#start --descr#flexfield#end --gl#accountff#start --gl#accountff#end FROM AS_LEADS AL, ORG_ORGANIZATION_DEFINITIONS OOD, FND_USER FU, HZ_PARTIES HP, AS_SALES_STAGES_VL ASSV, HZ_PARTY_SITES HPS, HZ_LOCATIONS HL, AS_SALES_METHODOLOGY_VL ASML -- AMS_SOURCE_CODES AMSC WHERE 1 AND AL.ORG_ID

=1 =OOD.ORGANIZATION_ID

AND AL.CREATED_BY

=FU.USER_ID

AND AL.CUSTOMER_ID

=HP.PARTY_ID

AND ASSV.SALES_STAGE_ID =AL.SALES_STAGE_ID AND AL.ORG_ID

=ASSV.ORG_ID

AND HPS.LOCATION_ID

=HL.LOCATION_ID

AND HPS.PARTY_SITE_ID

=AL.ADDRESS_ID

AND AL.SALES_METHODOLOGY_ID=ASML.SALES_METHODOLOGY_ID ;

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_PAYMENT_TRANS_V" ("PAYEE_SALESREP_ID", "SALESREP", "POSTING_BATCH_ID", "POSTING_TYPE", "TRX_TYPE", "ROLE_ID", "INCENTIVE_TYPE_CODE", "AMOUNT", "PROCESSED_DATE", "EVENT_FACTOR", "PAYMENT_FACTOR", "QUOTA_FACTOR", "COMMISSION_RATE", "PAYMENT_AMOUNT", "PAYRUN_ID", "COMM_PTD", "BONUS_PTD", "COMM_DRAW", "BONUS_DRAW", "COMM_PAID", "BONUS_PAID", "DRAW_PAID", "REASON", "BONUS_REASON", "RECOVERY_METHOD", "DRAW_PTD", "BONUS_GIVEN", "CPW_GUARANTEE", "ADJUST_PAID", "PAYMENT_WORKSHEET_ID", "PMT_AMOUNT_CALC", "CURRENT_EARNINGS_DUE", "CURRENT_EARNINGS", "BB_PRIOR_PERIOD_ADJ", "WORKSHEET_STATUS", "ORGANIZATION_NAME", "CREATION_DATE", "CREATED_BY_ID", "CREATED_BY", "SALESREP_NUMBER", "RS_SALESREP_ID", "PAYMENT_TRANSACTION_ID", "ORG_ID", "RS_ORG_ID", "ORGANIZATION_ID", "FU_USER_ID", "COPYRIGHT") AS SELECT CPT.PAYEE_SALESREP_ID, RS.NAME SALESREP, CPT.POSTING_BATCH_ID, CPT.POSTING_TYPE, CPT.TRX_TYPE, CPT.ROLE_ID, CPT.INCENTIVE_TYPE_CODE, CPT.AMOUNT, CPT.PROCESSED_DATE, CPT.EVENT_FACTOR,

CPT.PAYMENT_FACTOR, CPT.QUOTA_FACTOR, CPT.COMMISSION_RATE, CPT.PAYMENT_AMOUNT, CPT.PAYRUN_ID, CPW.COMM_PTD, CPW.BONUS_PTD, CPW.COMM_DRAW, CPW.BONUS_DRAW, CPW.COMM_PAID, CPW.BONUS_PAID, CPW.DRAW_PAID, CPW.REASON, CPW.BONUS_REASON, CPW.RECOVERY_METHOD, CPW.DRAW_PTD, CPW.BONUS_GIVEN, CPW.GUARANTEE CPW_GUARANTEE, CPW.ADJUST_PAID, CPW.PAYMENT_WORKSHEET_ID, CPW.PMT_AMOUNT_CALC, CPW.CURRENT_EARNINGS_DUE, CPW.CURRENT_EARNINGS, CPW.BB_PRIOR_PERIOD_ADJ, CPW.WORKSHEET_STATUS, OOD.ORGANIZATION_NAME , CPT.CREATION_DATE, CPT.CREATED_BY CREATED_BY_ID, FU.USER_NAME CREATED_BY,

RS.SALESREP_NUMBER, --PRIMARY KEYS RS.SALESREP_ID RS_SALESREP_ID, CPT.PAYMENT_TRANSACTION_ID, CPT.ORG_ID, RS.ORG_ID RS_ORG_ID, OOD.ORGANIZATION_ID, FU.USER_ID FU_USER_ID , 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.' Copyright --descr#flexfield#start --descr#flexfield#end --gl#accountff#start --gl#accountff#end FROM CN_PAYMENT_TRANSACTIONS CPT, RA_SALESREPS RS, CN_PAYMENT_WORKSHEETS CPW, ORG_ORGANIZATION_DEFINITIONS OOD, FND_USER FU WHERE 1

=1

AND CPT.PAYEE_SALESREP_ID=RS.SALESREP_ID AND CPT.payrun_ID AND CPT.ORG_ID AND CPT.CREATED_BY ;

=CPW.PAYRUN_ID =OOD.ORGANIZATION_ID =FU.USER_ID

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_PROPOSALS_TASKS_V" ("PROPOSAL_NAME", "PROPOSAL_DESC", "PROPOSAL_STATUS", "DUE_DATE", "CUSTOMER_NAME", "CUSTOMER_CONTACT", "TEMPLATE_NAME", "TEMPLATE_DESC", "CREATION_DATE", "CREATED_BY_ID", "CREATED_BY", "TASK_DUE_DATE", "TASK_NUMBER", "SUBJECT", "TYPE", "TASK_TYPE_ID", "TASK_STATUS", "TASK_STATUS_ID", "OWNER_ID", "OWNER", "OWNER_TYPE_CODE", "OWNER_TYPE", "PROPOSAL_ID", "PARTY_ID", "CONTACT_ID", "TEMPLATE_ID", "TASK_ID", "FU_USER_ID", "COPYRIGHT") AS SELECT PP.PROPOSAL_NAME, PP.PROPOSAL_DESC, PP.PROPOSAL_STATUS, PP.DUE_DATE, HP.PARTY_NAME CUSTOMER_NAME, HP1.PARTY_NAME CUSTOMER_CONTACT, PTV.TEMPLATE_NAME, PTV.TEMPLATE_DESC, PP.CREATION_DATE, PP.CREATED_BY CREATED_BY_ID, FU.USER_NAME CREATED_BY, JTV.SCHEDULED_END_DATE TASK_DUE_DATE, JTV.TASK_NUMBER, JTV.TASK_NAME SUBJECT, JTV.TASK_TYPE TYPE, JTV.TASK_TYPE_ID, JTV.TASK_STATUS TASK_STATUS, JTV.TASK_STATUS_ID, JTV.OWNER_ID, JTV.OWNER, JTV.OWNER_TYPE_CODE, JTV.OWNER_TYPE, ---Primary Keys

PP.PROPOSAL_ID, HP.PARTY_ID, HP1.PARTY_ID CONTACT_ID, PTV.TEMPLATE_ID, JTV.TASK_ID, FU.USER_ID FU_USER_ID , 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.' Copyright --descr#flexfield#start --descr#flexfield#end --gl#accountff#start --gl#accountff#end FROM PRP_PROPOSALS PP, HZ_PARTIES HP, HZ_PARTIES HP1, PRP_TEMPLATES_VL PTV, FND_USER FU, JTF_TASKS_V JTV WHERE 1

=1

AND PP.PARTY_ID

=HP.PARTY_ID

AND PP.CONTACT_PARTY_ID=HP1.PARTY_ID

;

AND PP.TEMPLATE_ID

=PTV.TEMPLATE_ID

AND PP.CREATED_BY

=FU.USER_ID

AND PP.PROPOSAL_ID

=JTV.SOURCE_OBJECT_ID

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_PROPOSALS_V" ("PROPOSAL_NAME", "PROPOSAL_DESC", "PROPOSAL_STATUS", "DUE_DATE", "CUSTOMER_NAME", "CUSTOMER_CONTACT", "TEMPLATE_NAME", "TEMPLATE_DESC", "CREATION_DATE", "CREATED_BY_ID", "CREATED_BY", "PROPOSAL_ID", "PARTY_ID", "CONTACT_ID", "TEMPLATE_ID", "FU_USER_ID", "COPYRIGHT") AS SELECT PP.PROPOSAL_NAME, PP.PROPOSAL_DESC, PP.PROPOSAL_STATUS, PP.DUE_DATE, HP.PARTY_NAME CUSTOMER_NAME, HP1.PARTY_NAME CUSTOMER_CONTACT, PTV.TEMPLATE_NAME, PTV.TEMPLATE_DESC, PP.CREATION_DATE, PP.CREATED_BY CREATED_BY_ID, FU.USER_NAME CREATED_BY, ---Primary Keys PP.PROPOSAL_ID, HP.PARTY_ID, HP1.PARTY_ID CONTACT_ID, PTV.TEMPLATE_ID, FU.USER_ID FU_USER_ID , 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.' Copyright --descr#flexfield#start --descr#flexfield#end --gl#accountff#start --gl#accountff#end FROM PRP_PROPOSALS PP, HZ_PARTIES HP,

HZ_PARTIES HP1, PRP_TEMPLATES_VL PTV, FND_USER FU WHERE 1

=1

AND PP.PARTY_ID

=HP.PARTY_ID

AND PP.CONTACT_PARTY_ID=HP1.PARTY_ID AND PP.TEMPLATE_ID

=PTV.TEMPLATE_ID

AND PP.CREATED_BY

=FU.USER_ID

;

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_QUOTE_DETAILS_V" ("OPPORTUNITY_NAME", "QUOTE_NAME", "SALES_ORDER_NUMBER", "ORG_ID", "QUOTE_SOURCE_CODE", "CUSTOMER", "ACCOUNT_NUMBER", "CONTACT", "CHANNEL_CODE", "AMOUNT", "EXPIRATION_DATE", "SALES_GROUP", "CURRENCY", "PRICE_LIST", "AGREEMENT", "ORDER_TYPE", "OPERATING_UNIT", "SALES_PERSON_NAME", "RESELLER", "RESELLER_CONTACT", "RESELLER_PHONE", "RESELLER_ADDRESS", "CONSULTING_PARTNER", "CONSULTING_PARTNER_CONTACT", "CONSULTING_PARTNER_PHONE", "CONSULTING_PARTNER_ADDRESS", "PRICE_LIST_ID", "ORDER_TYPE_ID", "QUOTE_HEADER_ID", "ORDER_ID", "LEAD_ID", "PARTY_ID", "RELATED_OBJECT_ID", "QUOTE_VERSION", "QUOTE_NUMBER", "CUST_ACCOUNT_ID", "LEAD_OPPORTUNITY_ID", "LEAD_CONTACT_ID", "HEADER_ID", "REP_ORG_ID", "SALES_CREDIT_ID", "GROUP_ID", "LIST_HEADER_ID", "AGREEMENT_ID", "TRANSACTION_TYPE_ID", "ORGANIZATION_ID", "SALES_LEAD_ID", "SALESREP_ID", "FCTL_CURRENCY_CODE", "COPYRIGHT") AS SELECT AL.DESCRIPTION OPPORTUNITY_NAME, AQH.QUOTE_NAME QUOTE_NAME, OOH.ORDER_NUMBER SALES_ORDER_NUMBER, AQH.ORG_ID ORG_ID, AQH.QUOTE_SOURCE_CODE QUOTE_SOURCE_CODE, HZ.PARTY_NAME CUSTOMER , HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER , -- AMSC.ARC_SOURCE_CODE_FOR SOURCE_NAME, /* decode (amsc.arc_source_code_for ,

'CAMP', ( SELECT campaign_name FROM AMS_CAMPAIGNS_ALL_TL WHERE campaign_id = amsc.source_code_for_id ), 'EVEH', ( SELECT event_header_name FROM AMS_EVENT_HEADERS_ALL_TL WHERE event_header_id = amsc.source_code_for_id ), 'EONE', ( SELECT event_offer_name FROM AMS_EVENT_OFFERS_ALL_TL WHERE event_offer_id = amsc.source_code_for_id ), 'EVEO', ( SELECT event_offer_name FROM AMS_EVENT_OFFERS_ALL_TL WHERE event_offer_id = amsc.source_code_for_id ), 'CSCH', ( SELECT schedule_name FROM ams_campaign_schedules_tl WHERE schedule_id = amsc.source_code_for_id ), 'OFFR', ( SELECT description FROM qp_list_headers_tl WHERE list_header_id = amsc.source_code_for_id

), null )source_name,*/ ( SELECT NVL(HPP.PERSON_NAME,NULL) FROM HZ_ORG_CONTACTS_CPUI_V HOC, HZ_PERSON_PROFILES_CPUI_V HPP WHERE ASLC.CONTACT_PARTY_ID = HOC.RELATIONSHIP_PARTY_ID AND HOC.OBJECT_ID

= ASLC.CUSTOMER_ID

AND HOC.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND HOC.SUBJECT_ID

= HPP.PARTY_ID

AND HOC.SUBJECT_TABLE_NAME = 'HZ_PARTIES' )CONTACT, AQH.SALES_CHANNEL_CODE CHANNEL_CODE, AQH.TOTAL_QUOTE_PRICE AMOUNT, AQH.QUOTE_EXPIRATION_DATE EXPIRATION_DATE, ASGV.name SALES_GROUP, FCTL.name CURRENCY, QPLV.name PRICE_LIST, OAV.name AGREEMENT, OTT.name ORDER_TYPE, HOU.name OPERATING_UNIT, REP.name SALES_PERSON_NAME, AQH.ATTRIBUTE1 RESELLER, AQH.ATTRIBUTE2 RESELLER_CONTACT, AQH.ATTRIBUTE3 RESELLER_PHONE, AQH.ATTRIBUTE4 RESELLER_ADDRESS, AQH.ATTRIBUTE5 CONSULTING_PARTNER, AQH.ATTRIBUTE6 CONSULTING_PARTNER_CONTACT,

AQH.ATTRIBUTE7 CONSULTING_PARTNER_PHONE, AQH.ATTRIBUTE8 CONSULTING_PARTNER_ADDRESS, AQH.PRICE_LIST_ID PRICE_LIST_ID, OOH.ORDER_TYPE_ID ORDER_TYPE_ID, AQH.QUOTE_HEADER_ID QUOTE_HEADER_ID, AQH.ORDER_ID ORDER_ID, --PRIMARY KEYS AL.LEAD_ID , HZ.PARTY_ID PARTY_ID, AQRO.RELATED_OBJECT_ID, AQH.QUOTE_VERSION, AQH.QUOTE_NUMBER QUOTE_NUMBER, -- AMSC.SOURCE_CODE_ID, HCA.CUST_ACCOUNT_ID, ASLP.LEAD_OPPORTUNITY_ID, ASLC.LEAD_CONTACT_ID, OOH.HEADER_ID, REP.ORG_ID REP_ORG_ID, ASSC.SALES_CREDIT_ID, ASGV.SALES_GROUP_ID GROUP_ID, QPLV.PRICE_LIST_ID LIST_HEADER_ID, OAV.AGREEMENT_ID, OTT.TRANSACTION_TYPE_ID, HOU.ORGANIZATION_ID, ASL.SALES_LEAD_ID SALES_LEAD_ID, REP.SALESREP_ID SALESREP_ID, FCTL.CURRENCY_CODE FCTL_CURRENCY_CODE , 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.' Copyright

--descr#flexfield#start --descr#flexfield#end --gl#accountff#start --gl#accountff#end FROM AS_LEADS AL, ASO_QUOTE_RELATED_OBJECTS AQRO, ASO_QUOTE_HEADERS AQH, -- AMS_SOURCE_CODES AMSC, HZ_PARTIES HZ, HZ_CUST_ACCOUNTS HCA, AS_SALES_LEADS ASL, AS_SALES_LEAD_OPPORTUNITY ASLP, AS_SALES_LEAD_CONTACTS ASLC, OE_ORDER_HEADERS OOH, RA_SALESREPS REP, AS_SALES_CREDITS ASSC, AS_SALES_GROUPS_V ASGV, FND_CURRENCIES_VL FCTL, QP_PRICE_LISTS_V QPLV, OE_AGREEMENTS_VL OAV, OE_TRANSACTION_TYPES_VL OTT, HR_OPERATING_UNITS HOU WHERE AQRO.QUOTE_OBJECT_TYPE_CODE = 'HEADER' AND AL.LEAD_ID

= AQRO.OBJECT_ID(+)

AND AQH.QUOTE_HEADER_ID AND AL.LEAD_ID

= AQRO.QUOTE_OBJECT_ID

= ASLP.OPPORTUNITY_ID

--- AND AL.SOURCE_PROMOTION_ID

= AMSC.SOURCE_CODE_ID(+)

AND ASLP.SALES_LEAD_ID(+) = ASL.SALES_LEAD_ID AND HZ.PARTY_ID (+)

= ASL.CUSTOMER_ID

AND HZ.PARTY_ID

= HCA.PARTY_ID

AND HCA.CUST_ACCOUNT_ID = AQH.CUST_ACCOUNT_ID AND ASL.SALES_LEAD_ID

= ASLC.SALES_LEAD_ID(+)

AND OOH.HEADER_ID

= AQH.ORDER_ID

AND OOH.SALESREP_ID

= REP.SALESREP_ID(+)

AND AL.LEAD_ID

= ASSC.LEAD_ID(+)

AND ASGV.SALES_GROUP_ID(+) = ASSC.SALESGROUP_ID and ASGV.manager_person_id(+) = ASSC.person_id AND FCTL.CURRENCY_CODE AND AQH.PRICE_LIST_ID

= QPLV.PRICE_LIST_ID(+)

AND AQH.CONTRACT_ID

= OAV.AGREEMENT_ID(+)

AND OOH.ORDER_TYPE_ID AND AQH.ORG_ID

= AQH. CURRENCY_CODE(+)

= OTT.TRANSACTION_TYPE_ID(+)

= HOU.ORGANIZATION_ID(+)

;

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_SALES_CUSTOMERS_V" ("PARTY_NUMBER", "NAME", "CREATION_DATE", "CREATED_BY_ID", "PARTY_TYPE", "D_U_N_S_NUMBER", "WEB_SITE", "EMAIL_ADDRESS", "ACCOUNT_NUMBER", "PARTY_SITE_NUMBER", "COUNTRY", "ADDRESS_LINE1", "ADDRESS_LINE2", "ADDRESS_LINE3", "ADDRESS_LINE4", "CITY", "COUNTY", "STATE", "PROVINCE", "POSTAL_CODE", "PURPOSE", "STATUS", "PARTY_ID", "CUST_ACCOUNT_ID", "PARTY_SITE_ID", "LOCATION_ID", "CUST_ACCT_SITE_ID", "SITE_USE_ID", "FU_USER_ID", "COPYRIGHT") AS SELECT HP.PARTY_NUMBER PARTY_NUMBER, HP.PARTY_NAME NAME, hp.creation_date creation_date, hp.created_by created_by_id, HP.PARTY_TYPE PARTY_TYPE, HP.DUNS_NUMBER D_U_N_S_NUMBER, HP.URL WEB_SITE, HP.EMAIL_ADDRESS EMAIL_ADDRESS,

HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER, HPS.PARTY_SITE_NUMBER, HL.COUNTRY, HL.ADDRESS1 ADDRESS_LINE1, HL.ADDRESS2 ADDRESS_LINE2, HL.ADDRESS3 ADDRESS_LINE3, HL.ADDRESS4 ADDRESS_LINE4, HL.CITY, HL.COUNTY, HL.STATE, HL.PROVINCE, HL.POSTAL_CODE, HCSUA.SITE_USE_CODE PURPOSE, DECODE(HCSUA.STATUS,'A','ACTIVE','NOT ACTIVE') STATUS, ---Primary Keys HP.PARTY_ID PARTY_ID, HCA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID, HPS.PARTY_SITE_ID, HL.LOCATION_ID, HCASA.CUST_ACCT_SITE_ID, HCSUA.SITE_USE_ID, FU.USER_ID FU_USER_ID , 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.' Copyright --descr#flexfield#start --descr#flexfield#end --gl#accountff#start --gl#accountff#end FROM HZ_PARTIES HP,

HZ_CUST_ACCOUNTS HCA, HZ_PARTY_SITES HPS, HZ_LOCATIONS HL, HZ_CUST_ACCT_SITES_ALL HCASA, HZ_CUST_SITE_USES_ALL HCSUA, FND_USER FU WHERE 1

=1

AND HP.PARTY_ID AND HCA.PARTY_ID

=HCA.PARTY_ID =HPS.PARTY_ID

AND HPS.LOCATION_ID

=HL.LOCATION_ID

AND HPS.PARTY_SITE_ID

=HCASA.PARTY_SITE_ID

AND HCASA.CUST_ACCT_SITE_ID=HCSUA.CUST_ACCT_SITE_ID AND HP.CREATED_BY

=FU.USER_ID

;

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_TASKS_V" ("TASK_NUMBER", "SUBJECT", "TYPE", "TASK_TYPE_ID", "TASK_STATUS", "TASK_STATUS_ID", "DUE_DATE", "OWNER_ID", "OWNER", "OWNER_TYPE_CODE", "OWNER_TYPE", "CUSTOMER_ID", "CUSTOMER_NAME", "CUSTOMER_NUMBER", "CREATED_BY", "CREATED_BY_ID", "CREATION_DATE", "TASK_ID", "FU_USER_ID", "COPYRIGHT") AS SELECT JTV.TASK_NUMBER, JTV.TASK_NAME SUBJECT, JTV.TASK_TYPE TYPE, JTV.TASK_TYPE_ID, JTV.TASK_STATUS TASK_STATUS, JTV.TASK_STATUS_ID, JTV.SCHEDULED_END_DATE DUE_DATE, JTV.OWNER_ID, JTV.OWNER,

JTV.OWNER_TYPE_CODE, JTV.OWNER_TYPE, JTV.CUSTOMER_ID, JTV.CUSTOMER_NAME, JTV.CUSTOMER_NUMBER, FU.USER_NAME CREATED_BY, JTV.CREATED_BY CREATED_BY_ID, JTV.CREATION_DATE CREATION_DATE, ---Primary Keys JTV.TASK_ID, FU.USER_ID FU_USER_ID , 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.' Copyright --descr#flexfield#start --descr#flexfield#end --gl#accountff#start --gl#accountff#end FROM FND_USER FU, JTF_TASKS_V JTV WHERE 1

=1

AND JTV.CREATED_BY =FU.USER_ID ;

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_TOP_TEN_OPPORTUNITY_V" ("OPPORTUNITY_NAME", "OPPORTUNITY_NUMBER", "CREATION_DATE", "CREATED_BY_ID", "CREATED_BY", "CUSTOMER", "PARTY_TYPE", "CUSTOMER_ID", "PARTY_NUMBER", "STATUS", "SALES_CHANNEL", "SOURCE_PROMOTION_ID", "CYCLE", "CYCLE_DESCRIPTION", "TOTAL_AMOUNT", "CURRENCY_CODE", "DECISION_TIMEFRAME_CODE", "PRODUCT_CATEGORY_ID", "PRODUCT_CAT_SET_ID", "PRODUCT_CATEGORY", "PRODUCT", "PRODUCT_UOM", "QUANTITY", "PRODUCT_AMOUNT", "EMPLOYEES_TOTAL", "LEAD_ID", "FU_USER_ID", "PARTY_ID",

"SALES_CHANNEL_CODE", "SALES_STAGE_ID", "LEAD_LINE_ID", "CATEGORY_ID", "INVENTORY_ITEM_ID", "ORGANIZATION_ID", "UOM_CODE", "ASSAT_LANGUAGE", "MUOMT_LANGUAGE", "COPYRIGHT") AS SELECT AL.DESCRIPTION OPPORTUNITY_NAME, AL.LEAD_NUMBER OPPORTUNITY_NUMBER, AL.CREATION_DATE, AL.CREATED_BY CREATED_BY_ID, FU.USER_NAME CREATED_BY, HP.PARTY_NAME CUSTOMER, HP.PARTY_TYPE, AL.CUSTOMER_ID, HP.PARTY_NUMBER, AL.STATUS STATUS, ASO_CHANNEL.SALES_CHANNEL SALES_CHANNEL, AL.SOURCE_PROMOTION_ID , ASSAT.NAME CYCLE, ASSAT.DESCRIPTION CYCLE_DESCRIPTION, AL.TOTAL_AMOUNT, AL.CURRENCY_CODE, AL.DECISION_TIMEFRAME_CODE, ALLA.PRODUCT_CATEGORY_ID, ALLA.PRODUCT_CAT_SET_ID, MC.DESCRIPTION PRODUCT_CATEGORY, NVL(MSIK.DESCRIPTION,MC.DESCRIPTION) PRODUCT, MUOMT.UNIT_OF_MEASURE PRODUCT_UOM, ALLA.QUANTITY, ALLA.TOTAL_AMOUNT PRODUCT_AMOUNT, HP.EMPLOYEES_TOTAL, -----PRIMARY KEYS

AL.LEAD_ID LEAD_ID, FU.USER_ID FU_USER_ID, HP.PARTY_ID, ASO_CHANNEL.SALES_CHANNEL_CODE, ASSAT.SALES_STAGE_ID, ALLA.LEAD_LINE_ID , MC.CATEGORY_ID, MSIK.INVENTORY_ITEM_ID, MSIK.ORGANIZATION_ID, MUOMT.UOM_CODE, ASSAT.LANGUAGE ASSAT_LANGUAGE, MUOMT.LANGUAGE MUOMT_LANGUAGE , 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.' Copyright --DESCR#FLEXFIELD#START --DESCR#FLEXFIELD#END --GL#ACCOUNTFF#START --GL#ACCOUNTFF#END FROM AS_LEADS AL, FND_USER FU, HZ_PARTIES HP, ASO_I_SALES_CHANNELS_V ASO_CHANNEL, AS_SALES_STAGES_ALL_TL ASSAT, AS_LEAD_LINES_ALL ALLA, MTL_CATEGORIES MC, MTL_SYSTEM_ITEMS_KFV MSIK, MTL_UNITS_OF_MEASURE_TL MUOMT WHERE AL.CREATED_BY

=FU.USER_ID

AND AL.CUSTOMER_ID

=HP.PARTY_ID

AND AL.CHANNEL_CODE

= ASO_CHANNEL.SALES_CHANNEL_CODE(+)

AND AL.SALES_STAGE_ID

=ASSAT.SALES_STAGE_ID(+)

AND ASSAT.LANGUAGE(+) AND AL.LEAD_ID

= USERENV('LANG')

=ALLA.LEAD_ID(+)

AND ALLA.PRODUCT_CATEGORY_ID=MC.CATEGORY_ID(+) AND ALLA.INVENTORY_ITEM_ID =MSIK.INVENTORY_ITEM_ID(+) AND ALLA.ORGANIZATION_ID =MSIK.ORGANIZATION_ID(+) AND ALLA.UOM_CODE

=MUOMT.UOM_CODE(+)

AND MUOMT.LANGUAGE(+) AND AL.DESCRIPTION

= USERENV('LANG')

IS NOT NULL

ORDER BY NVL(AL.TOTAL_AMOUNT,0) DESC ;

CREATE OR REPLACE FORCE VIEW "XXEIS"."EIS_ASN_WIN_OPPERTUNITY_V" ("OPPERTUNITY_NUMBER", "OPPERTUNITY_NAME", "CUSTOMER", "ADDRESS", "CLOSED_DATE", "METHODLOGY", "SATGE", "STAUS", "CLOSE_REASON", "WIN_PROBABILITY", "SALES_CHANNEL", "AMOUNT", "CURRENCY", "OPERATING_UNIT", "LEAD_ID", "PARTY_ID", "AL_SALES_METHODOLOGY_ID", "SALES_METHODOLOGY_ID", "SALES_STAGE_ID", "PARTY_SITE_ID", "LOCATION_ID", "ORGANIZATION_ID", "FCV_CURRENCY_CODE", "ASSA_LANGUAGE", "COPYRIGHT") AS SELECT AL.LEAD_NUMBER OPPERTUNITY_NUMBER , AL.DESCRIPTION OPPERTUNITY_NAME , HP.PARTY_NAME CUSTOMER , HL.ADDRESS1 ||HL.ADDRESS2 ||HL.ADDRESS3 ||HL.ADDRESS4 ||HL.CITY ||HL.COUNTY ||HL.STATE

||HL.COUNTRY ADDRESS , AL.DECISION_DATE CLOSED_DATE , ASMV.name METHODLOGY , ASSA.name SATGE , AL.STATUS STAUS , AL.CLOSE_REASON CLOSE_REASON , AL.WIN_PROBABILITY WIN_PROBABILITY , AL.CHANNEL_CODE SALES_CHANNEL , AL.TOTAL_AMOUNT AMOUNT , FCV.NAME CURRENCY , -- AMSC.ARC_SOURCE_CODE_FOR SOURCE_NAME , HOU.name OPERATING_UNIT /*, decode (amsc.arc_source_code_for , 'CAMP', ( SELECT campaign_name FROM AMS_CAMPAIGNS_ALL_TL WHERE campaign_id = amsc.source_code_for_id ), 'EVEH', ( SELECT event_header_name FROM AMS_EVENT_HEADERS_ALL_TL WHERE event_header_id = amsc.source_code_for_id ), 'EONE', ( SELECT event_offer_name FROM AMS_EVENT_OFFERS_ALL_TL WHERE event_offer_id = amsc.source_code_for_id ),

'EVEO', ( SELECT event_offer_name FROM AMS_EVENT_OFFERS_ALL_TL WHERE event_offer_id = amsc.source_code_for_id ), 'CSCH', ( SELECT schedule_name FROM ams_campaign_schedules_tl WHERE schedule_id = amsc.source_code_for_id ), 'OFFR', ( SELECT description FROM qp_list_headers_tl WHERE list_header_id = amsc.source_code_for_id ), NULL ) source_name*/ --Primary Keys , AL.LEAD_ID LEAD_ID , HP.PARTY_ID PARTY_ID , --- AMSC.SOURCE_CODE_ID SOURCE_CODE_ID , AL.SALES_METHODOLOGY_ID AL_SALES_METHODOLOGY_ID , ASMV.SALES_METHODOLOGY_ID SALES_METHODOLOGY_ID, ASSA.SALES_STAGE_ID SALES_STAGE_ID , HPS.PARTY_SITE_ID PARTY_SITE_ID , HL.LOCATION_ID LOCATION_ID, HOU.ORGANIZATION_ID, FCV.CURRENCY_CODE FCV_CURRENCY_CODE,

ASSA.LANGUAGE ASSA_LANGUAGE , 'Copyright(c) 2001-'||to_char(SYSDATE,'YYYY')||' '||'EiS Technologies Inc. All rights reserved.' Copyright --descr#flexfield#start --descr#flexfield#end --gl#accountff#start --gl#accountff#end FROM AS_LEADS AL, HZ_PARTIES HP, HZ_PARTY_SITES HPS, HZ_LOCATIONS HL, -- AMS_SOURCE_CODES AMSC, AS_SALES_METHODOLOGY_VL ASMV, AS_SALES_STAGES_ALL_TL ASSA, FND_CURRENCIES_VL FCV, HR_OPERATING_UNITS HOU WHERE HP.PARTY_ID

= AL.CUSTOMER_ID

AND AL.ADDRESS_ID

= HPS.PARTY_SITE_ID(+)

AND AL.CUSTOMER_ID

= HPS.PARTY_ID(+)

AND HPS.LOCATION_ID

= HL.LOCATION_ID(+)

-- AND AL.SOURCE_PROMOTION_ID

= AMSC.SOURCE_CODE_ID(+)

AND ASMV.SALES_METHODOLOGY_ID(+) = AL.SALES_METHODOLOGY_ID AND ASSA.SALES_STAGE_ID (+)

= AL.SALES_STAGE_ID

AND ASSA.LANGUAGE = USERENV('LANG') AND AL.CURRENCY_CODE

;

= FCV.CURRENCY_CODE

AND AL.ORG_ID

= HOU.ORGANIZATION_ID

AND AL.STATUS

='WON'