VIEW: APPS.CN_PAYROLL_WORKSHEET_V

Source

View Text - Preformatted

SELECT nvl(rre.source_name, cs.name) salesrep_name ,nvl(rre.source_number, cs.salesrep_number) employee_number ,sum(nvl(cpd.payment_amount ,0)) payment_amount ,rre.cost_center cost_center ,pet.element_name element_name ,cs.salesrep_id salesrep_id ,nvl(pet.element_type_id,cpd.pay_element_type_id) element_type_id ,p.payrun_id payrun_id FROM cn_posting_details_sum cpd ,cn_payment_worksheets pw ,jtf_rs_salesreps cs ,jtf_rs_resource_extns rre ,pay_element_types_f pet ,cn_repositories r ,gl_sets_of_books gl ,cn_payruns p WHERE pw.payrun_id = p.payrun_id AND pw.salesrep_id = cpd.credited_salesrep_id(+) AND pw.payrun_id = cpd.payrun_id(+) AND pw.salesrep_id = cs.salesrep_id AND nvl(cs.org_id, nvl( to_number (decode ( substrb (userenv ('client_info'), 1, 1 ), ' ', null, substrb( userenv('client_info'), 1, 10 ))), -99)) = nvl ( to_number ( decode (substrb( userenv ( 'client_info'), 1, 1 ), '', null, substrb( userenv ('client_info'), 1, 10 ))), -99) AND rre.resource_id =cs.resource_id AND cpd.pay_element_type_id = pet.element_type_id(+) AND ( p.pay_date between pet.effective_start_Date AND pet.effective_end_date OR ( pet.effective_start_Date is null AND pet.effective_end_date is null ) ) AND nvl(cpd.hold_flag, 'N') = 'N' AND nvl(cpd.waive_flag, 'N') = 'N' AND r.set_of_books_id = gl.set_of_books_id AND ( gl.currency_code = pet.input_currency_code or pet.input_currency_code is null ) GROUP BY nvl(rre.source_name,cs.name), nvl(rre.source_number, cs.salesrep_number), rre.cost_center, pet.element_name, cs.salesrep_id, nvl(pet.element_type_id,cpd.pay_element_type_id), p.payrun_id

View Text - HTML Formatted

SELECT NVL(RRE.SOURCE_NAME
, CS.NAME) SALESREP_NAME
, NVL(RRE.SOURCE_NUMBER
, CS.SALESREP_NUMBER) EMPLOYEE_NUMBER
, SUM(NVL(CPD.PAYMENT_AMOUNT
, 0)) PAYMENT_AMOUNT
, RRE.COST_CENTER COST_CENTER
, PET.ELEMENT_NAME ELEMENT_NAME
, CS.SALESREP_ID SALESREP_ID
, NVL(PET.ELEMENT_TYPE_ID
, CPD.PAY_ELEMENT_TYPE_ID) ELEMENT_TYPE_ID
, P.PAYRUN_ID PAYRUN_ID
FROM CN_POSTING_DETAILS_SUM CPD
, CN_PAYMENT_WORKSHEETS PW
, JTF_RS_SALESREPS CS
, JTF_RS_RESOURCE_EXTNS RRE
, PAY_ELEMENT_TYPES_F PET
, CN_REPOSITORIES R
, GL_SETS_OF_BOOKS GL
, CN_PAYRUNS P
WHERE PW.PAYRUN_ID = P.PAYRUN_ID
AND PW.SALESREP_ID = CPD.CREDITED_SALESREP_ID(+)
AND PW.PAYRUN_ID = CPD.PAYRUN_ID(+)
AND PW.SALESREP_ID = CS.SALESREP_ID
AND NVL(CS.ORG_ID
, NVL( TO_NUMBER (DECODE ( SUBSTRB (USERENV ('CLIENT_INFO')
, 1
, 1 )
, ' '
, NULL
, SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 10 )))
, -99)) = NVL ( TO_NUMBER ( DECODE (SUBSTRB( USERENV ( 'CLIENT_INFO')
, 1
, 1 )
, ''
, NULL
, SUBSTRB( USERENV ('CLIENT_INFO')
, 1
, 10 )))
, -99)
AND RRE.RESOURCE_ID =CS.RESOURCE_ID
AND CPD.PAY_ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID(+)
AND ( P.PAY_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE OR ( PET.EFFECTIVE_START_DATE IS NULL
AND PET.EFFECTIVE_END_DATE IS NULL ) )
AND NVL(CPD.HOLD_FLAG
, 'N') = 'N'
AND NVL(CPD.WAIVE_FLAG
, 'N') = 'N'
AND R.SET_OF_BOOKS_ID = GL.SET_OF_BOOKS_ID
AND ( GL.CURRENCY_CODE = PET.INPUT_CURRENCY_CODE OR PET.INPUT_CURRENCY_CODE IS NULL ) GROUP BY NVL(RRE.SOURCE_NAME
, CS.NAME)
, NVL(RRE.SOURCE_NUMBER
, CS.SALESREP_NUMBER)
, RRE.COST_CENTER
, PET.ELEMENT_NAME
, CS.SALESREP_ID
, NVL(PET.ELEMENT_TYPE_ID
, CPD.PAY_ELEMENT_TYPE_ID)
, P.PAYRUN_ID